PostgreSQL创建表

概述

表是PostgreSQL中存储数据的基本结构。本章介绍如何使用CREATE TABLE语句创建表。

CREATE TABLE语句

基本语法

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

简单示例

-- 创建简单的用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建产品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    description TEXT
);

数据类型

常用数据类型

类型	说明	示例
INTEGER	整数	42
SERIAL	自增整数	1, 2, 3...
VARCHAR(n)	变长字符串	'hello'
TEXT	无限长文本	'长文本...'
BOOLEAN	布尔值	TRUE, FALSE
DATE	日期	'2024-01-15'
TIMESTAMP	时间戳	'2024-01-15 10:30:00'
DECIMAL(p,s)	精确数值	99.99
JSON/JSONB	JSON数据	'{"key": "value"}'

列约束

常用约束

CREATE TABLE employees (
    -- 主键约束
    id SERIAL PRIMARY KEY,

    -- 非空约束
    name VARCHAR(100) NOT NULL,

    -- 唯一约束
    email VARCHAR(100) UNIQUE,

    -- 默认值
    status VARCHAR(20) DEFAULT 'active',

    -- 检查约束
    age INTEGER CHECK (age >= 18),

    -- 外键约束
    department_id INTEGER REFERENCES departments(id)
);

约束说明

约束	说明
PRIMARY KEY	主键，唯一标识每行
NOT NULL	不允许空值
UNIQUE	值必须唯一
DEFAULT	默认值
CHECK	检查条件
REFERENCES	外键引用

表级约束

复合主键

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

复合唯一约束

CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

命名约束

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    CONSTRAINT positive_price CHECK (price > 0),
    CONSTRAINT unique_name UNIQUE (name)
);

创建带外键的表

-- 先创建主表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 再创建从表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

外键选项

选项	说明
ON DELETE CASCADE	删除主记录时级联删除
ON DELETE SET NULL	删除主记录时设为NULL
ON DELETE RESTRICT	禁止删除有引用的主记录
ON UPDATE CASCADE	更新主键时级联更新

条件创建

-- 如果表不存在才创建
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

从查询创建表

-- 从现有表创建新表
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

-- 只复制结构不复制数据
CREATE TABLE users_backup (LIKE users INCLUDING ALL);

临时表

-- 创建临时表（会话结束自动删除）
CREATE TEMPORARY TABLE temp_results (
    id INTEGER,
    value TEXT
);

-- 或使用TEMP关键字
CREATE TEMP TABLE temp_data (
    id INTEGER,
    data JSONB
);

实际应用示例

电商数据库表结构

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 产品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单明细表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL
);

查看表结构

-- 使用psql命令
\d table_name
\d+ table_name  -- 详细信息

-- 使用SQL查询
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users';