Offensive Security Researcher & Quantum Cryptography Analyst

How to Create a Table in PostgreSQL

Abdul Wahab Junaid
How to Create a Table in PostgreSQL

Tables are where the actual work happens in any relational database — they’re the structures that hold your rows of data, enforce your rules about what’s valid, and define the relationships between different pieces of information. In this guide, I’ll go through everything involved in creating tables in PostgreSQL: the syntax, the data types you’ll actually use, constraints, keys, and a set of practical examples you can adapt for real projects.

The Basic Syntax of CREATE TABLE

At its simplest, creating a table looks like this:

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    column3 data_type constraints
);

Here’s a real, minimal example:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

This single statement creates a table called customers with three columns: an auto-incrementing ID that serves as the primary key, a required name field, and a required, unique email field. Let’s break down every piece of that.

Common PostgreSQL Data Types

Choosing the right data type for each column matters — it affects storage size, performance, and what kind of validation the database enforces automatically.

Numeric types:

Text types:

Date and time types:

Boolean type:

Other useful types:

Constraints Explained

Constraints are rules PostgreSQL enforces automatically on the data going into a column or table.

NOT NULL

Prevents a column from ever holding a null value.

name VARCHAR(100) NOT NULL

UNIQUE

Ensures no two rows share the same value in that column.

email VARCHAR(255) UNIQUE

PRIMARY KEY

A combination of NOT NULL and UNIQUE, plus it designates the column (or columns) as the table’s main identifier. Every table should generally have one.

id SERIAL PRIMARY KEY

FOREIGN KEY

Enforces that a value in one table must correspond to an existing value in another table, maintaining referential integrity between related tables.

customer_id INTEGER REFERENCES customers(id)

Or written more explicitly:

CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

CHECK

Enforces a custom condition on the values allowed in a column.

age INTEGER CHECK (age >= 0)

DEFAULT

Specifies a value to use automatically when none is provided during insertion.

created_at TIMESTAMPTZ DEFAULT NOW()

A More Complete Real-World Example

Here’s a table that pulls several of these concepts together — an orders table for a simple e-commerce system:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_number VARCHAR(20) UNIQUE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

This example shows:

Composite Primary Keys

Sometimes a single column isn’t enough to uniquely identify a row — this is common in junction tables that link two other tables in a many-to-many relationship.

CREATE TABLE order_items (
    order_id BIGINT REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

Here, the combination of order_id and product_id together forms the primary key, since neither column alone is guaranteed to be unique.

Table-Level vs. Column-Level Constraints

Constraints can be defined inline with the column (column-level) or separately at the end of the table definition (table-level). Both achieve the same result for single-column constraints, but table-level syntax is required for constraints spanning multiple columns, and it’s also useful for naming constraints explicitly.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    CONSTRAINT unique_sku UNIQUE (sku),
    CONSTRAINT positive_price CHECK (price > 0)
);

Naming constraints explicitly (like unique_sku and positive_price here) makes error messages more readable and makes it easier to modify or drop specific constraints later.

Creating a Table Only If It Doesn’t Already Exist

To avoid an error when a table might already exist — common in setup scripts that could run more than once:

CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Creating a Table Based on an Existing Table

PostgreSQL lets you create a new table using the structure (and optionally the data) of an existing one.

Structure only, no data:

CREATE TABLE customers_backup (LIKE customers INCLUDING ALL);

The INCLUDING ALL clause copies indexes, constraints, and defaults along with the column definitions — without it, you’d only get the bare column structure.

Structure and data together:

CREATE TABLE customers_backup AS
SELECT * FROM customers;

Note that this second approach does not copy constraints, indexes, or the primary key definition — only the column types and data. It’s useful for quick snapshots, not for creating a fully equivalent table.

Temporary Tables

For data that only needs to exist for the duration of a session (useful in complex queries or scripts), use CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE temp_results (
    id INTEGER,
    score NUMERIC
);

Temporary tables are automatically dropped when the session ends, and they’re invisible to other sessions even while they exist.

Viewing a Table’s Structure After Creation

Once a table is created, confirm its structure with:

\d table_name

This shows every column, its type, constraints, indexes, and foreign key relationships — a quick way to sanity-check that the table matches what you intended.

Common Use Cases

Normalized relational schemas. Breaking data into related tables (customers, orders, order_items, products) rather than one giant flat table is the standard approach for most transactional applications, and it relies heavily on foreign keys to maintain integrity.

Audit and logging tables. Tables with created_at and updated_at timestamp columns, often paired with triggers, are extremely common for tracking when records change.

Junction tables for many-to-many relationships. Whenever two entities can relate to each other multiple times in both directions (like students and courses), a junction table with a composite key is the standard pattern.

Troubleshooting Common Errors

ERROR: relation "table_name" already exists. The table name is already taken. Use CREATE TABLE IF NOT EXISTS, choose a different name, or drop the existing table first if appropriate.

ERROR: syntax error at or near .... Almost always a missing comma between column definitions, a missing closing parenthesis, or a misplaced constraint keyword. Read the statement column by column.

ERROR: type "sometype" does not exist. Usually a typo in a data type name, or an attempt to use a type from an extension that hasn’t been enabled yet (like citext or uuid, which sometimes require CREATE EXTENSION first).

ERROR: there is no unique constraint matching given keys for referenced table. This happens when a foreign key tries to reference a column in another table that isn’t a primary key or doesn’t have a unique constraint. Foreign keys must reference a column that’s guaranteed to be unique.

Best Practices

Indexes and CREATE TABLE

While indexes are technically a separate concept from table creation, it’s worth knowing that PostgreSQL automatically creates an index behind every PRIMARY KEY and UNIQUE constraint, since enforcing uniqueness requires an efficient way to check for duplicates. You can also define additional indexes right after creating the table:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Adding indexes on foreign key columns is a genuinely important habit — PostgreSQL does not automatically index foreign key columns the way some other databases do, and without an index, queries that join on that foreign key (or deletes that need to check for dependent rows) can end up doing slow full table scans as the table grows.

Using Generated Columns

PostgreSQL supports generated columns — columns whose value is automatically computed from other columns in the same row, rather than being set directly.

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    total_price NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

The STORED keyword means the value is calculated and physically saved to disk whenever the row is inserted or updated, rather than recalculated on every read. This is useful for values you’ll query or index frequently, since it avoids repeating the calculation and lets you build an index directly on the generated column.

Enum Types for Constrained Values

Rather than using a plain text column with a CHECK constraint to restrict values to a fixed set, PostgreSQL supports defining a proper enumerated type:

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

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

This has a couple of advantages over a CHECK constraint approach: the valid values are defined once and reused across any table that needs them, and PostgreSQL enforces the type at a lower level, which can be marginally more efficient. The trade-off is that adding a new valid value later requires an ALTER TYPE statement rather than just updating a CHECK constraint, and removing a value from an enum is notably more involved. For status-like fields that change occasionally, a CHECK constraint on a text column is sometimes more flexible in practice; for truly fixed sets of values, enums are a clean fit.

Schema-Qualified Table Names

Tables don’t have to live in the default public schema. For larger applications, organizing tables into schemas keeps things logically grouped:

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    total_amount NUMERIC(10, 2) NOT NULL
);

Reference the table with its fully qualified name (sales.orders) unless the schema is already part of your search_path. This becomes genuinely useful in larger systems where dozens or hundreds of tables benefit from logical grouping — sales.orders, inventory.products, auth.users, and so on — while still living in the same database and remaining fully joinable.

Adding Comments to Tables and Columns

Documentation embedded directly in the schema is easy to overlook but genuinely useful for teams:

COMMENT ON TABLE orders IS 'Stores customer purchase orders and their fulfillment status.';
COMMENT ON COLUMN orders.status IS 'Current fulfillment state of the order.';

These comments show up when inspecting the table with \d+ orders in psql, and many database documentation tools can extract them automatically to keep schema docs up to date without manual duplication.

Practical Full Example: A Blog Schema

Bringing several of these concepts together, here’s a small but realistic schema for a blogging platform:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    published BOOLEAN NOT NULL DEFAULT false,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published) WHERE published = true;

CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_name VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_comments_post_id ON comments(post_id);

Notice the partial index on posts(published) WHERE published = true — PostgreSQL supports indexing only a subset of rows matching a condition, which keeps the index smaller and faster for queries that only ever care about published posts.

Frequently Asked Questions

What’s the difference between VARCHAR(n) and TEXT? Functionally, very little in PostgreSQL — both store variable-length text, and there’s no meaningful performance difference between them. VARCHAR(n) enforces a maximum length at the database level, which can be useful as a data integrity guard; TEXT has no length limit. Many experienced PostgreSQL users default to TEXT and add a CHECK (LENGTH(column) <= n) constraint only where a length limit genuinely matters.

Should I always use SERIAL, or is UUID better for primary keys? It depends on your use case. SERIAL/BIGSERIAL integers are compact, fast to index, and easy to reason about, but they reveal the approximate row count and creation order, and don’t work well if you need to generate IDs outside the database (e.g., before an insert, in distributed systems). UUID primary keys avoid those issues and are common in distributed or multi-service architectures, at the cost of slightly larger storage and index size.

Can I add constraints after the table already exists? Yes, using ALTER TABLE:

ALTER TABLE orders ADD CONSTRAINT positive_amount CHECK (total_amount >= 0);

This is common when tightening data integrity rules on an existing table, though be aware that adding a constraint to a table with existing data will fail if any current rows violate it.

How many columns can a PostgreSQL table have? The hard limit is 1,600 columns per table, though in practice, tables with more than a few dozen columns are often a sign that the data could benefit from being normalized into multiple related tables instead.

Wrapping Up

CREATE TABLE is one of the commands you’ll type most often while working with PostgreSQL, and getting comfortable with data types, constraints, keys, and the surrounding tools like indexes and schemas pays off immediately — a well-designed table structure prevents whole categories of bugs before they ever happen. From here, the natural next step is populating that table with data, which is exactly what I cover in the next article on inserting data into a PostgreSQL table.

Exit mobile version