PostgreSQL

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

内容

简介
先决条件
创建新数据库
列出现有数据库
在数据库中创建表
如何在 PostgreSQL 中查看表
修改表
删除表
删除数据库
使用管理命令行工具创建和删除数据库
结论
常见问题解答

分享到

简介

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

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

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

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

本指南不会直接涉及 PostgreSQL 的模式概念，但了解它的存在是很有好处的。

相反，我们将专注于如何创建和销毁 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：列约束是可选的约束，用于进一步限制可以存储在列中的数据。例如，您可以要求条目不能为空、唯一或正整数。
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。
名称：特定学校物品的名称。
描述：该物品的简短描述。
制造商：物品制造商的名称。
颜色：物品的颜色。
库存：我们拥有的某种类型的学校用品的数量。这永远不应小于 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 表。在此表中，应存在以下列

员工 ID：唯一的员工识别号。
名字：教师的名字。
姓氏：教师的姓氏。
科目：教师受聘教授的科目。
年级：教师受聘教授的学生的年级。

使用以下 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 进行操作，则可以在此处结束，或跳至结论。如果您想了解如何从命令行创建和删除数据库，请继续阅读下一节。

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

如果您可以 shell 访问安装了 PostgreSQL 的服务器或集群，您可能可以使用一些额外的命令行工具来帮助创建和删除数据库。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

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

要按照本指南中的示例进行操作，您可以键入以下内容，使用默认区域设置和 UTF8 字符编码创建一个名为 school 的数据库：

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 文档了解更多信息。

在 PRISMA.IO 上的相关内容

当使用 Prisma 开发 PostgreSQL 时，您通常会使用 Prisma Migrate 创建数据库和表。您可以在我们关于使用 Prisma Migrate 进行开发的指南中学习如何使用它。

Prisma 是一个用于 Typescript 和 Node.js 的开源数据库工具包，旨在使应用程序开发人员在使用数据库时更高效、更自信。

常见问题解答

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;

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

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

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

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

关于作者

Justin Ellingwood

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

PostgreSQL 数据类型简介

在 PostgreSQL 中处理日期

在 GitHub 上编辑此页