PostgreSQL CREATE TABLE

Basic syntax

The CREATE TABLE statement defines a new table in your database. At minimum, you need a table name and at least one column:

CREATE TABLE users (
    id integer,
    name text
);

Each column gets a name and a data type. You can add as many columns as you need, separated by commas.

A more complete example:

CREATE TABLE users (
    id integer PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL,
    created_at timestamp NOT NULL DEFAULT now()
);

This creates a users table with a primary key, a unique email column, a required display name, and a timestamp that defaults to the current time.

Data types

PostgreSQL has a large set of built-in data types. Here are the ones you'll use most often.

Numeric types

| Type | Storage | Range | Use case | |------|---------|-------|----------| | smallint | 2 bytes | -32,768 to 32,767 | Small counters, status codes | | integer | 4 bytes | -2.1 billion to 2.1 billion | Most integer columns | | bigint | 8 bytes | -9.2 quintillion to 9.2 quintillion | Large IDs, row counts | | numeric(p, s) | variable | up to 131,072 digits | Money, precision math | | real | 4 bytes | 6 decimal digits precision | Scientific data (approximate) | | double precision | 8 bytes | 15 decimal digits precision | Scientific data (approximate) |

For most integer columns, integer is the right choice. Use bigint when you expect billions of rows or are working with external IDs that may exceed integer range.

For money and financial data, always use numeric. Floating point types (real, double precision) introduce rounding errors that will cause problems with currency.

CREATE TABLE products (
    id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL,
    weight_kg double precision,
    stock_count integer NOT NULL DEFAULT 0
);

Text types

| Type | Description | |------|-------------| | text | Variable-length string, no limit | | varchar(n) | Variable-length string, max n characters | | char(n) | Fixed-length string, padded with spaces |

In PostgreSQL, text and varchar perform identically. There is no performance benefit to using varchar(255) over text. The varchar(n) limit can be useful as a data integrity check (for example, ensuring a country code is no more than 2 characters), but if you're adding arbitrary limits like 255, you're better off using text and validating length in your application.

CREATE TABLE customers (
    id integer PRIMARY KEY,
    name text NOT NULL,
    country_code varchar(2) NOT NULL,
    bio text
);

Date and time types

| Type | Description | Example | |------|-------------|---------| | date | Calendar date | 2024-03-15 | | time | Time of day (no timezone) | 14:30:00 | | timestamp | Date and time (no timezone) | 2024-03-15 14:30:00 | | timestamptz | Date and time (with timezone) | 2024-03-15 14:30:00+00 | | interval | Time span | 2 hours 30 minutes |

Always prefer timestamptz over timestamp for storing points in time. A plain timestamp has no timezone information, which leads to confusion when your servers, users, or database sessions are in different time zones.

CREATE TABLE events (
    id integer PRIMARY KEY,
    title text NOT NULL,
    starts_at timestamptz NOT NULL,
    ends_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Boolean

The boolean type stores true, false, or NULL.

CREATE TABLE features (
    id integer PRIMARY KEY,
    name text NOT NULL,
    is_enabled boolean NOT NULL DEFAULT false
);

UUID

The uuid type stores 128-bit universally unique identifiers.

CREATE TABLE sessions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id integer NOT NULL,
    expires_at timestamptz NOT NULL
);

The gen_random_uuid() function is built into PostgreSQL 13 and later. For older versions, you'll need the uuid-ossp extension.

JSON and JSONB

PostgreSQL supports storing JSON data. Always use jsonb over json. The jsonb type stores data in a binary format that's faster to query and supports indexing.

CREATE TABLE orders (
    id integer PRIMARY KEY,
    customer_id integer NOT NULL,
    metadata jsonb DEFAULT '{}',
    line_items jsonb NOT NULL
);

Arrays

PostgreSQL supports array columns for any built-in type:

CREATE TABLE posts (
    id integer PRIMARY KEY,
    title text NOT NULL,
    tags text[] DEFAULT '{}',
    ratings integer[]
);

Arrays work well for small, simple lists. For more complex relationships, a separate junction table is usually the better approach.

Constraints

Constraints enforce rules on your data at the database level. They prevent bad data from getting into your tables, regardless of which application or script inserts the data.

NOT NULL

Requires that a column always has a value:

CREATE TABLE accounts (
    id integer PRIMARY KEY,
    email text NOT NULL,
    display_name text NOT NULL,
    bio text  -- this one can be NULL
);

Default for any column is nullable. You must explicitly add NOT NULL to make a column required. A good rule of thumb: start with NOT NULL on everything, then remove it only when you have a clear reason for allowing nulls.

UNIQUE

Ensures no two rows have the same value in a column:

CREATE TABLE users (
    id integer PRIMARY KEY,
    email text NOT NULL UNIQUE,
    username text NOT NULL UNIQUE
);

You can also create unique constraints across multiple columns:

CREATE TABLE team_members (
    team_id integer NOT NULL,
    user_id integer NOT NULL,
    role text NOT NULL DEFAULT 'member',
    UNIQUE (team_id, user_id)
);

This allows a user to be in many teams and a team to have many users, but the same user cannot be in the same team twice.

Note that NULL values are not considered equal in unique constraints. Two rows can both have NULL in a unique column. If you need to prevent duplicate nulls, use a unique index with COALESCE or a partial index.

CHECK

Validates that values meet a condition:

CREATE TABLE products (
    id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL CHECK (price >= 0),
    discount_pct integer CHECK (discount_pct BETWEEN 0 AND 100),
    sku text NOT NULL CHECK (length(sku) >= 3)
);

You can also name your constraints, which makes error messages more readable:

CREATE TABLE products (
    id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL,
    sale_price numeric(10, 2),
    CONSTRAINT positive_price CHECK (price >= 0),
    CONSTRAINT sale_below_price CHECK (sale_price IS NULL OR sale_price < price)
);

Named constraints appear in error messages:

ERROR: new row for relation "products" violates check constraint "sale_below_price"

DEFAULT

Sets a default value when no value is provided during insert:

CREATE TABLE orders (
    id integer PRIMARY KEY,
    status text NOT NULL DEFAULT 'pending',
    priority integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);

Defaults can be constant values, expressions, or function calls. Common defaults include now() for timestamps, gen_random_uuid() for UUIDs, and '{}' for empty JSON objects.

Primary keys

Every table should have a primary key. It uniquely identifies each row and PostgreSQL automatically creates an index on it.

Serial (auto-incrementing integer)

The traditional approach in PostgreSQL:

CREATE TABLE users (
    id serial PRIMARY KEY,
    email text NOT NULL UNIQUE
);

serial is shorthand that creates an integer column with an auto-incrementing sequence. There are three sizes:

| Type | Underlying type | Max value | |------|----------------|-----------| | smallserial | smallint | 32,767 | | serial | integer | 2,147,483,647 | | bigserial | bigint | 9,223,372,036,854,775,807 |

For most tables, serial is fine. If you expect more than 2 billion rows, use bigserial.

Identity columns (SQL standard)

The modern, SQL-standard way to create auto-incrementing columns. Preferred over serial in PostgreSQL 10 and later:

CREATE TABLE users (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

Or, if you sometimes need to set the ID manually:

CREATE TABLE users (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

GENERATED ALWAYS prevents manual inserts of the ID (you can override with OVERRIDING SYSTEM VALUE if needed). GENERATED BY DEFAULT allows manual inserts but auto-generates when no value is provided.

Why prefer identity over serial? Identity columns are part of the SQL standard, have clearer ownership semantics, and dropping the column automatically drops the sequence. With serial, the sequence is a separate object you may need to manage independently.

UUID primary keys

UUIDs are useful when you need IDs that are unique across systems, or when you don't want sequential IDs exposed to users:

CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id integer NOT NULL,
    total numeric(10, 2) NOT NULL
);

Tradeoffs compared to integer primary keys:

• UUIDs are 16 bytes vs 4 bytes for integer (or 8 bytes for bigint)
• Random UUIDs cause more index fragmentation
• UUIDs are safe to generate on the client side
• UUIDs don't reveal row counts or insertion order

For most applications, integer primary keys work well. UUIDs make sense for distributed systems, public-facing IDs, or when you need to generate IDs before inserting.

Foreign keys

Foreign keys create links between tables and enforce referential integrity. They guarantee that a referenced row exists.

CREATE TABLE users (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (id),
    total numeric(10, 2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

The REFERENCES users (id) clause means that every user_id value in the orders table must match an existing id in the users table. Trying to insert an order with a non-existent user_id will fail.

ON DELETE behavior

What happens when you delete a referenced row? You control this with ON DELETE:

-- Prevent deletion if orders exist (default)
user_id integer NOT NULL REFERENCES users (id) ON DELETE RESTRICT

-- Delete all orders when the user is deleted
user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE

-- Set user_id to NULL when the user is deleted
user_id integer REFERENCES users (id) ON DELETE SET NULL

-- Set user_id to its default value when the user is deleted
user_id integer NOT NULL DEFAULT 0 REFERENCES users (id) ON DELETE SET DEFAULT

CASCADE is convenient but dangerous. Deleting a user will silently delete all their orders, invoices, and anything else that cascades. For important data, RESTRICT (the default) is safer. It forces you to handle related data explicitly.

ON UPDATE behavior

The same options exist for updates. Most commonly used when the referenced column might change:

CREATE TABLE order_items (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    product_sku text NOT NULL REFERENCES products (sku) ON UPDATE CASCADE,
    quantity integer NOT NULL CHECK (quantity > 0)
);

If a product's SKU changes, all order_items referencing it will be updated automatically.

Named foreign key constraints

For complex tables, naming your foreign keys improves error messages:

CREATE TABLE order_items (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id integer NOT NULL,
    product_id integer NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
);

Indexing foreign key columns

PostgreSQL does not automatically create indexes on foreign key columns. You should add them yourself, especially for columns you'll join or filter on:

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (id),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_id ON orders (user_id);

Without this index, queries joining orders to users (and ON DELETE checks) will require a full table scan on orders.

Common patterns

Timestamps

Most tables benefit from created_at and updated_at columns:

CREATE TABLE articles (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

PostgreSQL doesn't automatically update updated_at. You need a trigger:

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

This trigger works on any table with an updated_at column. Create the function once, then add the trigger to each table that needs it.

Soft delete

Instead of deleting rows, mark them as deleted:

CREATE TABLE customers (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL,
    deleted_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

Then filter out deleted rows in your queries:

-- Active customers only
SELECT * FROM customers WHERE deleted_at IS NULL;

-- "Delete" a customer
UPDATE customers SET deleted_at = now() WHERE id = 42;

You can create a partial index to speed up queries on active rows:

CREATE INDEX idx_customers_active ON customers (email)
WHERE deleted_at IS NULL;

One important consideration: the UNIQUE constraint on email will prevent you from creating a new account with the same email as a soft-deleted one. If that's a problem, replace the unique constraint with a partial unique index:

-- Remove the column-level UNIQUE constraint, then:
CREATE UNIQUE INDEX idx_customers_email_active ON customers (email)
WHERE deleted_at IS NULL;

Enum-like status columns

You have two options for status columns: text with a check constraint, or a PostgreSQL enum type.

Using a check constraint:

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    created_at timestamptz NOT NULL DEFAULT now()
);

Using an enum type:

CREATE TYPE order_status AS ENUM (
    'pending', 'processing', 'shipped', 'delivered', 'cancelled'
);

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending',
    created_at timestamptz NOT NULL DEFAULT now()
);

Enums give you type safety and slightly better storage. Check constraints are more flexible (easier to add or remove values). For most cases, a check constraint is simpler and works fine.

CREATE TABLE AS

Create a new table from the results of a query:

CREATE TABLE active_users AS
SELECT id, email, display_name, created_at
FROM users
WHERE last_login_at > now() - interval '30 days';

This creates the table and populates it with data in one step. The new table has the same column types as the query results, but does not inherit constraints, indexes, or defaults from the source table.

You can also create an empty table with the same structure:

CREATE TABLE users_backup AS
SELECT * FROM users
WHERE false;

CREATE TABLE AS is useful for creating materialized snapshots, staging tables for data migrations, and reporting tables.

CREATE TABLE LIKE

Copy the structure of an existing table, including (optionally) its constraints, defaults, and indexes:

-- Copy column definitions only
CREATE TABLE users_archive (LIKE users);

-- Copy everything: defaults, constraints, indexes, etc.
CREATE TABLE users_archive (LIKE users INCLUDING ALL);

You can be selective about what to include:

CREATE TABLE users_archive (
    LIKE users
    INCLUDING DEFAULTS
    INCLUDING CONSTRAINTS
    EXCLUDING INDEXES
);

The options are INCLUDING DEFAULTS, INCLUDING CONSTRAINTS, INCLUDING INDEXES, INCLUDING STORAGE, INCLUDING COMMENTS, INCLUDING GENERATED, INCLUDING IDENTITY, and INCLUDING ALL.

Unlike CREATE TABLE AS, this creates an empty table. It copies structure, not data.

IF NOT EXISTS

Avoid errors when a table already exists:

CREATE TABLE IF NOT EXISTS users (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now()
);

If the table exists, PostgreSQL skips the statement and issues a notice instead of an error. This is useful in migration scripts and initialization code where you may not know if the table has already been created.

Note that IF NOT EXISTS only checks the table name. It does not verify that the existing table has the same columns or structure. If a users table exists with different columns, this statement will silently do nothing.

Temporary tables

Temporary tables exist only for the duration of your database session (or transaction). They're visible only to the session that created them.

CREATE TEMPORARY TABLE import_staging (
    raw_email text,
    raw_name text,
    row_number integer
);

-- Process the data
INSERT INTO import_staging (raw_email, raw_name, row_number)
VALUES ('ALICE@EXAMPLE.COM', '  Alice  ', 1);

-- Clean and insert into the real table
INSERT INTO users (email, display_name)
SELECT lower(trim(raw_email)), trim(raw_name)
FROM import_staging;

To drop the temporary table at the end of the transaction instead of the session:

CREATE TEMPORARY TABLE import_staging (
    raw_email text,
    raw_name text
) ON COMMIT DROP;

Temporary tables are useful for complex data transformations, staging imported data, and storing intermediate results in multi-step queries.

Unlogged tables

Unlogged tables skip write-ahead logging (WAL), making inserts and updates significantly faster. The tradeoff: data is lost if PostgreSQL crashes or restarts.

CREATE UNLOGGED TABLE analytics_buffer (
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    received_at timestamptz NOT NULL DEFAULT now()
);

Good use cases for unlogged tables:

• Caches and session data that can be rebuilt
• Staging tables for ETL pipelines
• Temporary analytics buffers
• Build or processing queues where loss is acceptable

Do not use unlogged tables for data you can't afford to lose.

Partitioned tables

Table partitioning splits a large table into smaller pieces while keeping a single logical table for queries. PostgreSQL supports three partitioning strategies.

Range partitioning

The most common approach. Splits data by value ranges, typically dates:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_type text NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE events_2024_q3 PARTITION OF events
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE events_2024_q4 PARTITION OF events
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

Queries that filter on created_at will only scan the relevant partitions.

List partitioning

Splits data by a list of values:

CREATE TABLE logs (
    id bigint GENERATED ALWAYS AS IDENTITY,
    severity text NOT NULL,
    message text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY LIST (severity);

CREATE TABLE logs_info PARTITION OF logs
    FOR VALUES IN ('info', 'debug');

CREATE TABLE logs_warning PARTITION OF logs
    FOR VALUES IN ('warning');

CREATE TABLE logs_error PARTITION OF logs
    FOR VALUES IN ('error', 'critical');

Hash partitioning

Distributes rows evenly across partitions using a hash function:

CREATE TABLE cache_entries (
    key text NOT NULL,
    value jsonb NOT NULL,
    expires_at timestamptz
) PARTITION BY HASH (key);

CREATE TABLE cache_entries_0 PARTITION OF cache_entries
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE cache_entries_1 PARTITION OF cache_entries
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE cache_entries_2 PARTITION OF cache_entries
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE cache_entries_3 PARTITION OF cache_entries
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partitioning adds complexity. It's worth considering when a table exceeds tens of millions of rows or when you need to efficiently drop old data (detaching a partition is instant, compared to deleting millions of rows).

Schema qualification

By default, tables are created in the public schema. You can specify a different schema:

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    amount numeric(10, 2) NOT NULL,
    issued_at timestamptz NOT NULL DEFAULT now()
);

Schemas are useful for organizing tables by domain, managing permissions, and separating application concerns.

Putting it all together

Here's a realistic set of tables for an e-commerce application:

-- Users
CREATE TABLE users (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    password_hash text NOT NULL,
    display_name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- Products
CREATE TABLE products (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    description text,
    price numeric(10, 2) NOT NULL CHECK (price >= 0),
    sku text NOT NULL UNIQUE,
    stock_count integer NOT NULL DEFAULT 0 CHECK (stock_count >= 0),
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- Orders
CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    total numeric(10, 2) NOT NULL CHECK (total >= 0),
    shipping_address jsonb,
    notes text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

-- Order items
CREATE TABLE order_items (
    id integer GENERATED ALWAYS AS IDENTITY 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 numeric(10, 2) NOT NULL CHECK (unit_price >= 0),
    UNIQUE (order_id, product_id)
);

CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

This setup gives you referential integrity, input validation, sensible defaults, and the indexes you need for common queries. If you're working with a schema like this, a GUI database client like DB Pro can help you visualize table relationships and run queries without switching between the terminal and your editor.

Common mistakes

Forgetting to index foreign keys

PostgreSQL creates an index on the primary key automatically, but not on foreign key columns. If you join on orders.user_id or delete a user (which checks for referencing orders), PostgreSQL will do a full table scan without an index.

Always create indexes on your foreign key columns.

Using serial when you should use bigserial

Two billion sounds like a lot, but high-traffic tables can hit the serial limit faster than expected. If you're creating a table that will have many inserts (events, logs, analytics), use bigserial or bigint GENERATED ALWAYS AS IDENTITY from the start. Changing the column type later requires a table rewrite.

Choosing varchar(255) out of habit

This is a MySQL convention that has no benefit in PostgreSQL. The database doesn't allocate 255 bytes per row. text and varchar(255) perform identically. Use text unless you have a specific length limit you want to enforce.

Missing NOT NULL constraints

Every nullable column is a potential source of bugs. NULL values propagate through expressions in unexpected ways (NULL + 1 is NULL, NULL = NULL is NULL, not true). Add NOT NULL to every column that should always have a value.

Storing money as floating point

Never use real or double precision for monetary values:

-- Bad: floating point errors
CREATE TABLE payments (
    amount double precision NOT NULL  -- 0.1 + 0.2 = 0.30000000000000004
);

-- Good: exact precision
CREATE TABLE payments (
    amount numeric(10, 2) NOT NULL  -- 0.1 + 0.2 = 0.30
);

Not setting ON DELETE behavior on foreign keys

The default is RESTRICT, which prevents deleting a parent row if children exist. This is often the right choice, but you should make it an explicit decision. Think about what should happen when the parent row is deleted and set ON DELETE accordingly.

Creating tables without a primary key

Every table needs a primary key. Without one, you have no guaranteed way to identify a specific row. Updates and deletes become ambiguous. Replication tools may not work. ORMs will complain.

Even junction tables should have a primary key:

CREATE TABLE user_roles (
    user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    role_id integer NOT NULL REFERENCES roles (id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

Quick reference

| Command | Description | |---------|-------------| | CREATE TABLE name (...) | Create a new table | | CREATE TABLE IF NOT EXISTS name (...) | Create only if it doesn't exist | | CREATE TABLE name AS SELECT ... | Create from query results | | CREATE TABLE name (LIKE other INCLUDING ALL) | Copy structure from another table | | CREATE TEMPORARY TABLE name (...) | Create session-scoped table | | CREATE UNLOGGED TABLE name (...) | Create table without WAL | | CREATE TABLE name (...) PARTITION BY RANGE (col) | Create partitioned table | | DROP TABLE name | Remove a table | | DROP TABLE IF EXISTS name | Remove if it exists | | ALTER TABLE name ADD COLUMN ... | Add a column | | ALTER TABLE name DROP COLUMN ... | Remove a column |

Summary

Creating tables well means thinking about your data before writing any application code. Pick the right data types, add constraints to prevent bad data, index your foreign keys, and use sensible defaults.

Start with NOT NULL on everything, use timestamptz for timestamps, prefer text over varchar(n), and add created_at and updated_at to most tables. The few extra minutes spent on a well-designed schema will save you hours of debugging later.