16 CREATE TABLE

PostgreSQL 通过表存储数据。CREATE TABLE 通过列出列及其数据类型来定义新表。默认值、约束、索引和外键都可以在创建时添加，但最小的表只需要一个名称和列清单。本课保持简洁；约束会在专门的课程中介绍。

本章要点

• 使用 CREATE TABLE 定义表
• 为每列选择数据类型（integer、text 等）
• 使用 DROP TABLE IF EXISTS 使建表脚本幂等
• 检查 public 模式中的表
• TEMP TABLE、普通表和 UNLOGGED TABLE 的区别

示例

-- Drop the table if it exists so the script is idempotent
DROP TABLE IF EXISTS fruits;

-- A minimal table: id and name
CREATE TABLE fruits (
  id   integer,
  name text
);

-- Confirm it exists in the current schema
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename = 'fruits';

-- See the columns and their types
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'fruits'
ORDER BY ordinal_position;

fruits 有两列：id（32 位有符号整数）和 name（变长文本）。CREATE TABLE 中列的顺序，就是行的物理存储顺序——为了提高宽表的存储效率，应将定长列放在一起。对于小表，这并不重要。

DROP TABLE IF EXISTS 只在表存在时删除表；如果没有 IF EXISTS，该语句在全新的数据库中就会报错。将它与 CREATE TABLE 结合后，在迭代数据库模式的过程中可以安全地反复运行脚本。当然，在生产环境中不要盲目删除表——请使用迁移。

有几种 CREATE TABLE 的变体值得了解：

• CREATE TEMP TABLE（或 CREATE TEMPORARY TABLE）创建的表只在当前会话中存在，并会自动删除。
• CREATE UNLOGGED TABLE 不写入预写式日志——写入更快，但服务器崩溃时内容会被清空。适合存放可重建的派生数据或暂存数据。
• CREATE TABLE ... (LIKE other_table INCLUDING ALL) 会复制另一个表的模式。
• CREATE TABLE ... AS SELECT ... 会根据查询创建并填充一个表（称为 CTAS）。

运行示例

运行方式：

$ psql -f source/create-table.sql postgres
DROP TABLE
CREATE TABLE
 schemaname | tablename
------------+-----------
 public     | fruits
(1 row)

 column_name | data_type | is_nullable
-------------+-----------+-------------
 id          | integer   | YES
 name        | text      | YES
(2 rows)

常见陷阱

• 不要因为在 MySQL 中学到 varchar(255) 就选择它：在 PostgreSQL 中，text 和 varchar(n) 的性能相同。除非你明确需要长度上限，否则使用 text。
• 忘记在本不应为空的列上添加 NOT NULL——以后可以再加，但从一开始就正确设置更容易。
• 将列命名为 user、order、select——这些都是保留字。可以改名（user_id、placed_at），也可以加引号（"user"），但这样会一直带来麻烦。

下载

SQL 文件：create-table.sql · 全部示例文件 · 准备工作