如何在 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 数据库显示在系统上的其他数据库中。这是概览服务器或集群中数据库的好方法。

在数据库中创建表

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

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

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

\c school

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

\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)

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

如何在 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 命令非常相似，但它作用于现有表。

ALTER TABLE <table_name> <change_command> <change_parameters>

ALTER TABLE some_table ADD COLUMN missing_column text;

ALTER TABLE some_table DROP COLUMN useless_column;

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

删除表

如果您希望删除表，可以使用 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 访问权限，您可能有权访问一些额外的命令行工具，这些工具可以帮助创建和删除数据库。安装 PostgreSQL 时，createdb 和 dropdb 命令与 PostgreSQL 捆绑在一起。

createdb db_name

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

createdb --encoding=UTF8 school

dropdb database_name

dropdb --if-exists database_name

dropdb school

结论

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

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

常见问题

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

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

createdb db_name

DROP DATABASE database_name;

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