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

简介

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

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

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

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

本指南不会直接涉及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权限的用户。

创建一个新数据库

连接到PostgreSQL实例后，无论是使用psql还是任何其他SQL客户端，您都可以使用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访问权限，您可能可以使用一些额外的命令行工具来帮助创建和删除数据库。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;