PostgreSQL

如何在PostgreSQL中创建和删除数据库和表

简介

PostgreSQL及其他关系型数据库管理系统使用数据库和表来构造和组织数据。我们可以快速回顾这两个术语的定义

数据库：彼此分离不同结构和数据集
表：定义数据结构并在数据库中存储实际数据值

在PostgreSQL中，数据库和表之间还有一个中间对象，称为schema（模式）

schema（模式）：数据库中的一个命名空间，包含表、索引、视图和其他项目。

本指南不会直接涉及PostgreSQL的schema概念，但了解它的存在是好的。

相反，我们将重点介绍如何创建和删除PostgreSQL数据库和表。示例主要使用SQL，但在结尾处，我们将向您展示如何使用命令行完成其中一些任务。这些替代方法使用标准PostgreSQL安装中包含的工具，如果您具有PostgreSQL主机的管理访问权限，则这些工具可用。

本指南中涵盖的一些语句，特别是PostgreSQL的CREATE TABLE语句，有许多额外选项，超出了本文的范围。如果您想了解更多信息，请查阅官方PostgreSQL文档。

先决条件

要遵循本指南，您需要使用psql命令行客户端以具有管理权限的用户身份登录到PostgreSQL实例。您的PostgreSQL实例可以本地安装、远程安装或由提供商配置。

具体来说，您的PostgreSQL用户需要CREATE DB权限或是一个Superuser（超级用户），您可以在psql中使用\du元命令进行检查。

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres超级用户在安装时会自动创建，它拥有所需的权限，但您可以使用任何具有Create DB权限的用户。

创建一个新数据库

一旦您使用psql或任何其他SQL客户端连接到PostgreSQL实例，您就可以使用SQL创建数据库。

创建数据库的基本语法是

CREATE DATABASE db_name;

这将使用默认数据库设置在当前服务器上创建一个名为db_name的数据库，并将当前用户设置为新数据库的所有者。您可以使用以下psql元命令查看默认template1模板的属性

\l template1

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(1 row)

您可以添加额外的参数来更改数据库的创建方式。以下是一些常见选项

ENCODING: 设置数据库的字符编码。
LC_COLLATE: 设置数据库的排序规则，即排序顺序。这是一个本地化选项，用于确定项目在排序时如何组织。
LC_CTYPE: 设置新数据库的字符分类。这是一个本地化选项，影响哪些字符被视为大写、小写和数字。

这些可以帮助确保数据库能够以您计划支持的格式和您项目的本地化偏好存储数据。

例如，要确保您的数据库支持Unicode，并覆盖服务器本身的区域设置以使用美式英语本地化（这些恰好与上面所示的template1中的值匹配，因此实际上不会发生任何更改），您可以输入

CREATE DATABASE db_name
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

要遵循本指南中的示例，请使用您的实例默认的区域设置和UTF8字符编码创建一个名为school的数据库

CREATE DATABASE school ENCODING 'UTF8';

这将使用您提供的规范创建新数据库。

列出现有数据库

要确定服务器或集群上当前可用的数据库，您可以使用以下SQL语句

SELECT datname FROM pg_database;

这将列出环境中当前定义的所有数据库

  datname
-----------
 _dodb
 template1
 template0
 defaultdb
 school
(5 rows)

如前所述，如果您使用psql客户端连接，您还可以使用\l元命令获取此信息

\l

这将显示可用的数据库名称及其所有者、编码、区域设置和权限

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

我们创建的school数据库显示在系统中的其他数据库中。这是获取服务器或集群中数据库概览的好方法。

在数据库中创建表

创建了一个或多个数据库后，您可以开始定义表来存储数据。表由名称和定义的模式组成，模式决定了每个记录必须包含的字段和数据类型。

PostgreSQL `CREATE TABLE` 语法

您可以使用CREATE TABLE语句创建表。该命令的简化基本语法如下所示

CREATE TABLE table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

上述语法中的组成部分包括以下内容

CREATE TABLE table_name: 定义表的创建基本语句。其中table_name占位符应替换为您希望使用的表名。
column_name TYPE: 定义表中的基本列。其中的column_name占位符应替换为您希望用于列的名称。TYPE指定了该列的PostgreSQL数据类型。存储在表中的数据必须符合列结构和列数据类型才能被接受。
column_constraint: 列约束是可选的限制，用于对可以存储在列中的数据施加进一步的限制。例如，您可以要求条目不能为null、必须唯一或必须是正整数。
table_constraints: 表约束与列约束类似，但涉及多列的交互。例如，您可以有一个表约束，检查表中的DATE_OF_BIRTH是否在DATE_OF_DEATH之前。

使用`IF NOT EXISTS`子句有条件地创建表

默认情况下，如果您尝试在PostgreSQL中创建数据库中已存在的表，则会发生错误。为了解决您希望在表不存在时创建表，但在表已存在时继续执行的问题，您可以使用IF NOT EXISTS子句。IF NOT EXISTS是可选限定符，它告诉PostgreSQL如果数据库已经存在则忽略该语句。

要使用IF NOT EXISTS子句，请将其插入到CREATE TABLE语法之后和表名之前的命令中

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

此变体将尝试创建表。如果指定数据库中已存在同名表，PostgreSQL将抛出警告，指示表名已被占用，而不是因错误而失败。

如何在PostgreSQL中创建表

上述语法足以创建基本表。例如，我们将在school数据库中创建两个表。一个表名为supplies，另一个表名为teachers

在supplies表中，我们希望包含以下字段

ID: 每种学校用品的唯一ID。
Name: 某个特定学校物品的名称。
Description: 物品的简短描述。
Manufacturer: 物品制造商的名称。
Color: 物品的颜色。
Inventory: 某种学校用品的库存数量。此数量不应小于0。

我们可以使用以下SQL语句创建具有上述属性的supplies表。

首先，通过键入以下命令，在psql中切换到您创建的school数据库

\c school

这将更改我们未来命令所针对的数据库。您的提示符应随之更改以反映该数据库。

接下来，使用以下语句创建supplies表

CREATE TABLE supplies (
  id INT PRIMARY KEY,
  name VARCHAR,
  description VARCHAR,
  manufacturer VARCHAR,
  color VARCHAR,
  inventory int CHECK (inventory > 0)
);

这将在school数据库中创建supplies表。PRIMARY KEY列约束是一种特殊约束，用于指示可以唯一标识表中记录的列。因此，该约束规定该列不能为空且必须唯一。PostgreSQL为主键列创建索引以提高查询速度。

通过键入以下命令验证新表是否存在

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
(1 row)

通过键入以下命令验证模式是否反映了预期的设计

\d supplies

                      Table "public.supplies"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 id           | integer           |           | not null |
 name         | character varying |           |          |
 description  | character varying |           |          |
 manufacturer | character varying |           |          |
 color        | character varying |           |          |
 inventory    | integer           |           |          |
Indexes:
    "supplies_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "supplies_inventory_check" CHECK (inventory > 0)

我们可以看到我们指定的每个列和数据类型。我们为inventory列定义的列约束列在末尾。

接下来，我们将创建一个teachers表。此表中应包含以下列

Employee ID（员工ID）: 唯一的员工识别号。
First name（名）: 教师的名。
Last name（姓）: 教师的姓。
Subject（科目）: 教师受聘教授的科目。
Grade level（年级）: 教师受聘教授的学生年级。

使用以下SQL语句创建具有上述模式的teachers表

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR,
  grade_level int
);

如何创建带主键和外键的表

您可以在我们的其他PostgreSQL指南中找到有关创建带主键和外键的表的信息。主键和外键都是PostgreSQL中的数据库约束类型。

主键是保证在同一表中的行之间唯一的一列或多列。所有主键都可以用于唯一标识特定行。主键不仅确保每行对于主键列具有唯一值，还确保这些列不包含NULL值。通常，PostgreSQL中的主键使用以下格式指定自动分配的递增主键：id SERIAL PRIMARY KEY。

外键是确保一个表中的一列或多列与另一个表中包含的值匹配的一种方式。这有助于确保表之间的参照完整性。

如何在PostgreSQL中查看表

在PostgreSQL中，您可以根据所需信息以几种不同的方式列出表。

如果您想查看数据库中可用的表，可以使用psql客户端附带的\dt元命令列出所有表，如我们上面演示的那样

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
 public | teachers | table | doadmin
(2 rows)

您还可以检查表的模式是否符合您的规范

\d teachers

                     Table "public.teachers"
   Column    |       Type        | Collation | Nullable | Default
-------------+-------------------+-----------+----------+---------
 id          | integer           |           | not null |
 first_name  | character varying |           |          |
 last_name   | character varying |           |          |
 subject     | character varying |           |          |
 grade_level | integer           |           |          |
Indexes:
    "teachers_pkey" PRIMARY KEY, btree (id)

teachers表似乎符合我们的定义。

修改表

如果您需要更改PostgreSQL中现有表的模式，可以使用ALTER TABLE命令。ALTER TABLE命令与CREATE TABLE命令非常相似，但它作用于现有表。

修改表语法

修改PostgreSQL表的基本语法如下

ALTER TABLE <table_name> <change_command> <change_parameters>

<change_command>表示您想要进行的精确更改类型，无论是设置不同的表选项、添加或删除列，还是更改类型或约束。<change_parameters>命令部分包含PostgreSQL完成更改所需的任何附加信息。

向表中添加列

您可以使用ADD COLUMN更改命令向PostgreSQL表添加列。更改参数将包括列名、类型和选项，就像您在CREATE TABLE命令中指定它们一样。

例如，要将一个名为missing_column的text类型列添加到名为some_table的表中，您可以输入

ALTER TABLE some_table ADD COLUMN missing_column text;

从表中删除列

如果相反，您想删除现有列，可以使用DROP COLUMN命令。您需要指定要删除的列名作为更改参数

ALTER TABLE some_table DROP COLUMN useless_column;

更改列的数据类型

要更改PostgreSQL用于特定列的数据类型，您可以使用ALTER COLUMN更改命令和SET DATA TYPE列命令。参数包括列名、其新类型以及一个可选的USING子句，用于指定旧类型如何转换为新类型。

例如，要使用显式转换将resident表中id列的值设置为int，我们可以输入以下内容

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

其他表更改

使用ALTER TABLE命令可以实现许多其他类型的更改。有关可用选项的更多信息，请查看官方PostgreSQL ALTER TABLE 文档。

删除表

如果您希望删除表，可以使用DROP TABLE SQL语句。这将删除表以及其中存储的任何数据。

基本语法如下

DROP TABLE table_name;

这将删除表（如果存在），如果表名不存在则抛出错误。

如果您希望在表存在时删除它，而在表不存在时不做任何操作，则可以在语句中包含IF EXISTS限定符

DROP TABLE IF EXISTS table_name;

默认情况下，具有其他表或对象依赖关系的表无法删除。为避免错误，您可以选择包含CASCADE参数，它会自动与表一起删除所有依赖关系

DROP TABLE table_name CASCADE;

如果任何表具有引用您正在删除的表的外键约束，该约束将自动被删除。

通过键入以下命令删除我们之前创建的supplies表

DROP TABLE supplies;

我们将保留teachers数据库，以证明删除数据库的语句也会删除所有子对象，如表。

删除数据库

DROP DATABASE语句告诉PostgreSQL删除指定的数据库。基本语法如下所示

DROP DATABASE database_name;

将database_name占位符替换为您要删除的数据库名称。如果找到该数据库，它将被删除。如果找不到该数据库，将发生错误

DROP DATABASE some_database;

ERROR:  database "some_database" does not exist

如果您希望在数据库存在时删除它，否则不执行任何操作，请包含可选的IF EXISTS选项

DROP DATABASE IF EXISTS some_database;

NOTICE:  database "some_database" does not exist, skipping
DROP DATABASE

这将删除数据库，如果找不到则不执行任何操作。

要删除本指南中使用的school数据库，请列出系统上现有的数据库

\l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

打开到您不想删除的其中一个数据库的新连接

\c defaultdb

新连接打开后，使用以下命令删除school数据库

DROP DATABASE school;

这将删除school数据库以及其中定义的teachers表。

如果您一直使用SQL进行操作，您可以在此处结束或跳到结论。如果您想了解如何从命令行创建和删除数据库，请继续阅读下一节。

使用管理命令行工具创建和删除数据库

如果您对PostgreSQL安装的服务器或集群具有shell访问权限，您可能可以使用一些额外的命令行工具来帮助创建和删除数据库。createdb和dropdb命令随PostgreSQL安装时捆绑在一起。

从命令行创建新数据库

createdb命令的基本语法（应由具有PostgreSQL管理访问权限的系统用户运行）是

createdb db_name

这将在PostgreSQL中使用默认设置创建一个名为db_name的数据库。

该命令也接受选项以改变其行为，就像您之前看到的SQL变体一样。您可以通过man createdb了解更多关于这些选项的信息。其中一些最重要的选项是

--encoding= : 设置数据库的字符编码。
--locale= : 设置数据库的区域设置。

这些可以帮助确保数据库能够以您计划支持的格式和您项目的本地化偏好存储数据。

例如，为了确保您的数据库以Unicode支持创建，并覆盖服务器自身的区域设置以使用美式英语本地化，您可以键入

createdb --encoding=UTF8 --locale=en_US db_name

假设您具有正确的权限，数据库将根据您的规范创建。

要遵循本指南中的示例，您可以通过键入以下命令创建一个名为school的数据库，使用默认区域设置和UTF8字符编码

createdb --encoding=UTF8 school

然后，您可以像往常一样连接到数据库，使用psql设置您的表。

从命令行删除数据库

dropdb命令与DROP DATABASE SQL语句相呼应。它具有以下基本语法

dropdb database_name

将database_name占位符更改为要删除的数据库名称。

默认情况下，如果找不到指定的数据库，此命令将导致错误。为了避免这种情况，您可以包含可选的--if-exists标志

dropdb --if-exists database_name

如果指定的数据库存在，它将被删除。否则，它将不执行任何操作。

要删除我们之前创建的school数据库，请输入

dropdb school

这将删除数据库以及其中包含的任何子元素（如表）。

结论

本文介绍了如何在PostgreSQL中创建和删除数据库和表的基本知识。这些是设置数据库系统和定义数据结构所需的一些最基本命令。

如前所述，本PostgreSQL教程中涵盖的SQL语句，特别是CREATE TABLE语句，有许多额外的参数可用于更改PostgreSQL的行为。您可以通过查阅官方PostgreSQL文档了解更多信息。

常见问题

PostgreSQL 在使用 `CREATE DATABASE` 命令时是否支持 `IF NOT EXISTS` 子句？

是的，PostgreSQL 支持在创建数据库和表时使用IF NOT EXISTS。以下演示了在创建表时使用该子句。

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

如何在PostgreSQL中从转储文件创建数据库？

要从转储文件（pg_dump）创建数据库，PostgreSQL提供了实用程序pg_restore。

该程序将数据库恢复到转储时的状态。示例语法如下所示

pg_restore [connection-option...][option...][filename]

哪个命令行命令在PostgreSQL中创建数据库？

要在PostgreSQL中创建数据库，请使用createdb命令。语法如下

createdb db_name

如何在PostgreSQL中删除数据库？

DROP DATABASE语句告诉PostgreSQL删除指定的数据库。基本语法如下所示

DROP DATABASE database_name;

如何在PostgreSQL中更改列的数据类型？

要更改特定列的数据类型，请使用ALTER COLUMN更改命令和SET DATA TYPE列命令。

基本语法包括列名、新类型，以及一个可选的USING子句，用于指定旧类型的转换方式。

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

关于作者

贾斯汀·埃林伍德

自2013年以来，贾斯汀一直致力于撰写关于数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。

如何在RDS上配置PostgreSQL数据库

PostgreSQL数据类型简介

在 GitHub 上编辑此页面

PostgreSQL

如何在PostgreSQL中创建和删除数据库和表

目录

简介

先决条件

创建一个新数据库

列出现有数据库

在数据库中创建表

PostgreSQL `CREATE TABLE` 语法

使用`IF NOT EXISTS`子句有条件地创建表

如何在PostgreSQL中创建表

如何创建带主键和外键的表

如何在PostgreSQL中查看表

修改表

修改表语法

向表中添加列

从表中删除列

更改列的数据类型

其他表更改

删除表

删除数据库

使用管理命令行工具创建和删除数据库

从命令行创建新数据库

从命令行删除数据库

结论

常见问题

贾斯汀·埃林伍德

Prisma的数据指南

PostgreSQL

如何在PostgreSQL中创建和删除数据库和表

目录

简介

先决条件

创建一个新数据库

列出现有数据库

在数据库中创建表

PostgreSQL CREATE TABLE 语法

使用IF NOT EXISTS子句有条件地创建表

如何在PostgreSQL中创建表

如何创建带主键和外键的表

如何在PostgreSQL中查看表

修改表

修改表语法

向表中添加列

从表中删除列

更改列的数据类型

其他表更改

删除表

删除数据库

使用管理命令行工具创建和删除数据库

从命令行创建新数据库

从命令行删除数据库

结论

常见问题

贾斯汀·埃林伍德

Prisma的数据指南

PostgreSQL `CREATE TABLE` 语法

使用`IF NOT EXISTS`子句有条件地创建表