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

简介

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

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

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

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

本指南不会直接处理 PostgreSQL 的模式概念，但了解它的存在很重要。

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

本指南中介绍的一些语句，特别是 PostgreSQL 的CREATE TABLE语句，具有许多其他选项，这些选项超出了本文的范围。如果您想了解更多信息，请查看PostgreSQL 官方文档。

先决条件

要按照本指南进行操作，您需要使用psql命令行客户端登录到具有管理权限的用户所在的 PostgreSQL 实例。您的 PostgreSQL 实例可以在本地、远程安装，或由提供商提供。

具体来说，您的 PostgreSQL 用户需要CREATE DB权限，或者是一个超级用户，您可以使用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)

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

• 编码：设置数据库的字符编码。
• 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 访问权限，您可能可以使用一些其他命令行工具来帮助创建和删除数据库。 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;