PostgreSQL: PostgreSQL数据表创建与修改

-- Create users table with email uniqueness and auto-timestamp
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create products table with price > 0 check
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB DEFAULT '{}'::jsonb
);

-- Create orders table with foreign keys and status check
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- Named foreign key with cascade
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    content TEXT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
);

-- Single-column primary key (most common)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Composite primary key (for junction tables)
CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (product_id, tag_id)
);

CREATE TABLE

-- Order items: delete order -> delete all its items (CASCADE)
-- Product: delete product -> set product_id to NULL (SET NULL)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id) ON DELETE SET NULL,
    quantity INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE

-- Price must be positive, discount cannot exceed price
CREATE TABLE promotions (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    discount_price DECIMAL(10, 2) CHECK (discount_price > 0),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    -- PG CHECK can reference other columns in the same row!
    CHECK (end_date > start_date)
    -- 注意：CHECK 不能跨表引用子查询，跨表校验需用触发器
);

CREATE TABLE

-- Add a new column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add with default value
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Change column type (requires USING for incompatible types)
ALTER TABLE users ALTER COLUMN phone TYPE TEXT;

-- Rename column
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- Set default value
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;

-- Add CHECK constraint
ALTER TABLE products ADD CONSTRAINT price_reasonable
    CHECK (price < 100000);

-- Drop a column
ALTER TABLE users DROP COLUMN IF EXISTS phone_number;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- SQL 语句执行成功

-- Safe delete (no error if table doesn't exist)
DROP TABLE IF EXISTS test_table;

-- Cascade delete (also drops dependent objects like views)
DROP TABLE IF EXISTS users CASCADE;

-- SQL 语句执行成功

# List all tables in current database
\dt

# Show users table structure
\d users

                                        Table "public.users"
   Column    |          Type          | Collation | Nullable |              Default
-------------+------------------------+-----------+----------+-----------------------------------
 id          | integer                |           | not null | nextval('users_id_seq'::regclass)
 email       | character varying(255) |           | not null |
 name        | character varying(100) |           | not null |
 password_hash | character(60)        |           | not null |
 created_at  | timestamp with time zone |         |          | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

-- ============================================
-- Complete example: e-commerce core tables
-- Users, Products, Orders with proper constraints
-- ============================================

-- 1. Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    role VARCHAR(20) DEFAULT 'customer'
        CHECK (role IN ('customer', 'admin', 'manager')),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Products table
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),
    category VARCHAR(50),
    attributes JSONB DEFAULT '{}'::jsonb,
    is_available BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. Orders table with foreign keys
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    shipping_address TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Order items table (junction table)
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) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
    subtotal DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- 5. Insert sample data
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$dummyhashforalice1234567890abcdefghijklmnopqr'),
    ('bob@example.com', 'Bob', '$2a$12$dummyhashforbob1234567890abcdefghijklmnopqrstuv');

INSERT INTO products (name, price, stock, category, attributes) VALUES
    ('Running Shoes', 89.99, 150, 'Footwear', '{"color": "red", "size": 42}'::jsonb),
    ('Laptop Backpack', 49.99, 300, 'Accessories', '{"color": "black", "material": "nylon"}'::jsonb);

-- 6. Verify table structure
\d users
\d products
\d orders
\d order_items