PostgreSQL Creating and Altering Tables

Q: What's the difference between SERIAL and IDENTITY, and which should I use?

SERIAL is PG-proprietary syntax that internally creates a sequence and sets a default. IDENTITY is SQL-standard syntax (PG 10+), with stricter behavior (you cannot manually insert a value unless OVERRIDING SYSTEM VALUE). For new projects, IDENTITY is recommended.

Q: VARCHAR or TEXT—which to choose?

In PG, VARCHAR and TEXT have identical performance. VARCHAR(n) has a length limit, TEXT has none. Suggestion: use VARCHAR when there's a clear length limit (e.g., email VARCHAR(255)), and TEXT when there's no limit (e.g., article body). Don't use VARCHAR without a length—that equals TEXT.

Q: TIMESTAMP or TIMESTAMPTZ?

Always use TIMESTAMPTZ (with time zone) is recommended. It stores UTC time and automatically converts to the client's time zone on display. TIMESTAMP has no time zone and causes confusion in cross-time-zone apps.

Q: Is the foreign-key cascade delete CASCADE safe?

CASCADE is convenient in dev (delete user → delete orders → delete order items, three-level cascade), but be cautious in production—accidentally deleting one user could delete large amounts of data. Suggestion: use RESTRICT for critical business tables, CASCADE for log/temporary tables.

Q: Does ALTER TABLE on a large table lock the table?

Simple operations (ADD COLUMN with a default, increasing VARCHAR length) don't lock the table. But changing data type, adding a NOT NULL constraint, etc., require a full table scan and will lock the table. For large tables, use CREATE INDEX CONCURRENTLY (covered in a later lesson) or run during a maintenance window.

Q: What's the maximum number of columns in a table?

A PG table can have up to 1600 columns (in practice often fewer due to the 8KB row-size limit). But more than 50 columns is usually a design problem—consider splitting the table or using JSONB to store sparse fields.

A table is the most central object in a database—it's the "spreadsheet" that stores your data, where each row is a record and each column is a field.

1. What You'll Learn

CREATE TABLE to create a table (column definitions, constraints, defaults)
A quick tour of PostgreSQL's common data types
PRIMARY KEY / FOREIGN KEY / UNIQUE / NOT NULL / CHECK constraints
ALTER TABLE to modify table structure
DROP TABLE to delete a table
psql \d to inspect table structure

2. A Real Story from an E-Commerce Team

(1) The Pain: How to Design Three Core Tables

Alice's team got a new requirement: create 3 core tables for the e-commerce system—users (users), products (products), and orders (orders). The requirements are:

Users table: email unique, password not null, registration time auto-recorded
Products table: price must be greater than 0, stock cannot be negative
Orders table: linked to user and product, status limited to a fixed set of values

Alice was unsure: where should the constraints go? How to choose data types? How to set up foreign-key cascade deletes?

(2) The Solution: CREATE TABLE + Constraints

PostgreSQL lets you declare all constraints right at table creation, so the database guarantees your data quality for you:

SQL

-- 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()
);

(3) The Payoff

Data quality guaranteed at the database level (impossible to insert a product with price < 0)
Foreign-key cascade delete (deleting a user automatically deletes all their orders)
Defaults reduce application-layer code (created_at auto-filled)

3. CREATE TABLE Syntax

(1) Full Syntax Structure

SQL

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

(2) Column-Level Constraints

Constraint	Syntax	Description
PRIMARY KEY	`column_name type PRIMARY KEY`	Primary key (unique + not null)
NOT NULL	`column_name type NOT NULL`	Disallow NULL
UNIQUE	`column_name type UNIQUE`	Values must be unique
DEFAULT	`column_name type DEFAULT value`	Default value
CHECK	`column_name type CHECK (condition)`	Condition check
REFERENCES	`column_name type REFERENCES table(col)`	Foreign key reference

(3) Table-Level Constraints

SQL

-- 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
);

4. Data Types at a Glance

PostgreSQL supports over 40 data types; the most common are below:

Category	Type	Description	Example
Integer	`SMALLINT`	2 bytes, -32768 ~ 32767	age, small quantities
	`INTEGER` (INT)	4 bytes, -2.1 billion ~ 2.1 billion	common integer (recommended default)
	`BIGINT`	8 bytes, ±9.2 trillion	large IDs, amounts (in cents)
Auto-increment	`SERIAL`	Auto-increment INTEGER	primary key ID (recommended)
	`BIGSERIAL`	Auto-increment BIGINT	large-table primary key
	`IDENTITY`	SQL-standard auto-increment (PG 10+)	recommended for new projects
Floating point	`REAL`	4 bytes, 6 digits precision	scientific computing
	`DOUBLE PRECISION`	8 bytes, 15 digits precision	scientific computing
Fixed point	`DECIMAL(p,s)`	Exact decimal	price, amount (recommended)
	`NUMERIC(p,s)`	Same as DECIMAL	same as above
String	`VARCHAR(n)`	Variable length, with upper bound	name, email
	`TEXT`	Variable length, no upper bound	description, content
	`CHAR(n)`	Fixed length	hash value, code
Boolean	`BOOLEAN`	true / false / null	flag bits
Date	`DATE`	date	birthday
	`TIMESTAMP`	date+time (no time zone)	local time
	`TIMESTAMPTZ`	date+time+time zone	global apps (recommended)
	`INTERVAL`	time interval	duration calculation
UUID	`UUID`	128-bit UUID	distributed ID
JSON	`JSON`	text JSON	good compatibility
	`JSONB`	binary JSON (recommended)	fast indexed queries

💡 Tip: In PostgreSQL, VARCHAR and TEXT have no performance difference (unlike MySQL). In most cases VARCHAR (with a length limit) or TEXT (no limit) is recommended.

5. Constraints in Detail

(1) PRIMARY KEY

graph TB
    PK[PRIMARY KEY] --> U[UNIQUE<br/>No duplicate values]
    PK --> NN[NOT NULL<br/>Cannot be empty]
    PK --> IDX[Auto Index<br/>B-Tree index created automatically]

▶ Example: Single-Column and Composite Primary Key

SQL

-- 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)
);

Output:

TEXT

CREATE TABLE

(2) FOREIGN KEY

Cascade action	ON DELETE behavior	ON UPDATE behavior
`CASCADE`	Cascade delete (delete user → delete their orders)	Cascade update
`SET NULL`	Set to NULL	Set to NULL
`SET DEFAULT`	Set to default value	Set to default value
`RESTRICT`	Reject delete (default behavior)	Reject update
`NO ACTION`	Same as RESTRICT (SQL standard)	Same as RESTRICT

▶ Example: Foreign Key and Cascade Actions

SQL

-- 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
);

Output:

TEXT

CREATE TABLE

(3) CHECK Constraint

▶ Example: CHECK Constraint Protecting Data Quality

SQL

-- 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)
    -- Note: CHECK cannot reference a subquery across tables; cross-table validation needs a trigger
);

Output:

TEXT

CREATE TABLE

6. ALTER TABLE

(1) Common Modification Operations

Operation	Syntax	Description
Add column	`ALTER TABLE t ADD COLUMN col type`	Add a column
Drop column	`ALTER TABLE t DROP COLUMN col`	Remove a column
Rename column	`ALTER TABLE t RENAME COLUMN old TO new`	Rename a column
Change type	`ALTER TABLE t ALTER COLUMN col TYPE new_type`	Change data type
Set default	`ALTER TABLE t ALTER COLUMN col SET DEFAULT val`	Set default value
Drop default	`ALTER TABLE t ALTER COLUMN col DROP DEFAULT`	Remove default value
Set NOT NULL	`ALTER TABLE t ALTER COLUMN col SET NOT NULL`	Make not null
Drop NOT NULL	`ALTER TABLE t ALTER COLUMN col DROP NOT NULL`	Allow null
Add constraint	`ALTER TABLE t ADD CONSTRAINT name CHECK (...)`	Add CHECK
Rename table	`ALTER TABLE old_name RENAME TO new_name`	Rename table

▶ Example: Modify Table Structure

SQL

-- 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;

Output:

TEXT

-- SQL statement executed successfully

⚠️ Note: Changing a column type may require rewriting the entire table (e.g., VARCHAR(50)→VARCHAR(100) doesn't, but INTEGER→TEXT does). On large tables, use ALTER TABLE ... ALTER COLUMN ... TYPE ... USING ... and make sure the USING clause converts correctly.

7. DROP TABLE

▶ Example: Drop a Table

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;

Output:

TEXT

-- SQL statement executed successfully

Option	Description
`IF EXISTS`	No error if the table doesn't exist
`CASCADE`	Also drops objects depending on this table (views, foreign key references, etc.)
`RESTRICT`	Rejects deletion when there are dependent objects (default behavior)

🔥 Common mistake: DROP TABLE is irreversible! All data is permanently lost. Always pg_dump a backup first in production.

8. Inspecting Table Structure with psql

Command	Function	Example
`\dt`	List all tables	`\dt`
`\dt+`	List tables (with size info)	`\dt+`
`\d tablename`	Show table structure	`\d users`
`\d+ tablename`	Show table structure (detailed)	`\d+ users`

▶ Example: Inspect Table Structure

BASH

# List all tables in current database
\dt

# Show users table structure
\d users

Output:

TEXT

                                        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)

9. Complete Example: E-Commerce Core Three Tables

SQL

-- ============================================
-- 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

❓ FAQ

Q What's the difference between SERIAL and IDENTITY, and which should I use?

A SERIAL is PG-proprietary syntax that internally creates a sequence and sets a default. IDENTITY is SQL-standard syntax (PG 10+), with stricter behavior (you cannot manually insert a value unless OVERRIDING SYSTEM VALUE). For new projects, IDENTITY is recommended.

Q VARCHAR or TEXT—which to choose?

A In PG, VARCHAR and TEXT have identical performance. VARCHAR(n) has a length limit, TEXT has none. Suggestion: use VARCHAR when there's a clear length limit (e.g., email VARCHAR(255)), and TEXT when there's no limit (e.g., article body). Don't use VARCHAR without a length—that equals TEXT.

Q TIMESTAMP or TIMESTAMPTZ?

A Always use TIMESTAMPTZ (with time zone) is recommended. It stores UTC time and automatically converts to the client's time zone on display. TIMESTAMP has no time zone and causes confusion in cross-time-zone apps.

Q Is the foreign-key cascade delete CASCADE safe?

A CASCADE is convenient in dev (delete user → delete orders → delete order items, three-level cascade), but be cautious in production—accidentally deleting one user could delete large amounts of data. Suggestion: use RESTRICT for critical business tables, CASCADE for log/temporary tables.

Q Does ALTER TABLE on a large table lock the table?

A Simple operations (ADD COLUMN with a default, increasing VARCHAR length) don't lock the table. But changing data type, adding a NOT NULL constraint, etc., require a full table scan and will lock the table. For large tables, use CREATE INDEX CONCURRENTLY (covered in a later lesson) or run during a maintenance window.

Q What's the maximum number of columns in a table?

A A PG table can have up to 1600 columns (in practice often fewer due to the 8KB row-size limit). But more than 50 columns is usually a design problem—consider splitting the table or using JSONB to store sparse fields.

📖 Summary

CREATE TABLE defines table structure; column definitions include data type + constraint + default
Data type choice: INTEGER/SERIAL for integers, DECIMAL for money, TIMESTAMPTZ for time, JSONB for flexible structures
Five main constraints: PRIMARY KEY / FOREIGN KEY / UNIQUE / NOT NULL / CHECK
Foreign-key cascades: CASCADE (cascade) / SET NULL / RESTRICT (default)—choose per business need
ALTER TABLE can add/drop/rename columns and modify types and constraints
DROP TABLE is irreversible; IF EXISTS prevents errors; CASCADE drops dependent objects
psql \d shows table structure, \dt lists all tables

📝 Exercises

Basic (★): Create a categories table (id, name, description, created_at), where name must be NOT NULL and UNIQUE. After inserting 3 test rows, use \d categories to inspect the table structure.
Intermediate (★★): Based on the e-commerce three tables created in this lesson, use ALTER TABLE to add a last_login_at TIMESTAMPTZ column and a login_count INTEGER DEFAULT 0 column to the users table. Then add a CHECK constraint ensuring login_count cannot be negative.
Challenge (★★★): Create a product_reviews table with: id (primary key), product_id (foreign key referencing products), user_id (foreign key referencing users), rating (1–5, CHECK constraint), title, content, created_at. Requirements: when a user is deleted, keep the review but set user_id to NULL; when a product is deleted, cascade-delete all its reviews.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Creating and Altering Tables

1. What You'll Learn

2. A Real Story from an E-Commerce Team

(1) The Pain: How to Design Three Core Tables

(2) The Solution: CREATE TABLE + Constraints

(3) The Payoff

3. CREATE TABLE Syntax

(1) Full Syntax Structure

(2) Column-Level Constraints

(3) Table-Level Constraints

4. Data Types at a Glance

5. Constraints in Detail

(1) PRIMARY KEY

▶ Example: Single-Column and Composite Primary Key

(2) FOREIGN KEY

▶ Example: Foreign Key and Cascade Actions

(3) CHECK Constraint

▶ Example: CHECK Constraint Protecting Data Quality

6. ALTER TABLE

(1) Common Modification Operations

▶ Example: Modify Table Structure

7. DROP TABLE

▶ Example: Drop a Table

8. Inspecting Table Structure with psql

▶ Example: Inspect Table Structure

9. Complete Example: E-Commerce Core Three Tables

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好