PostgreSQL - CREATE

PostgreSQL - Creating Tables

The CREATE TABLE statement is the fundamental command for defining the structure of your data in PostgreSQL. It creates a new, initially empty table in the current database. This involves naming the table, defining its columns, specifying the data type for each column, and applying constraints.

Syntax

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

Key components:

• table_name: A unique name for your table within the schema.
• column_name: The name of a column.
• data_type: The type of data the column will store (e.g., INTEGER, TEXT, TIMESTAMP).
• column_constraint: A rule applied to a single column (e.g., NOT NULL, UNIQUE).
• table_constraint: A rule applied to one or more columns (e.g., PRIMARY KEY, FOREIGN KEY).

Modern Data Types and Best Practices

Choosing the right data type is essential for data integrity, performance, and storage efficiency. Here are some commonly used modern types:

• Identity Columns: BIGINT GENERATED ALWAYS AS IDENTITY is the modern, SQL-standard way to create auto-incrementing primary keys. Prefer this over the older SERIAL type.
• Text: Use TEXT for strings of variable, unlimited length. Use VARCHAR(n) only if you need to enforce a specific maximum length.
• Numeric: For financial calculations, always use NUMERIC(precision, scale) to avoid floating-point rounding errors. Use REAL or DOUBLE PRECISION for scientific calculations.
• Date and Time: TIMESTAMP WITH TIME ZONE (alias TIMESTAMPTZ) is the best practice for storing timestamps. It stores the time in UTC and converts it to the client’s time zone upon retrieval, eliminating ambiguity.
• Unique Identifiers: UUID is excellent for generating unique keys in distributed systems.
• JSON: JSONB is the preferred type for storing JSON data. It’s stored in a decomposed binary format, which is faster to query and supports indexing.

Example: Creating Related Tables

Let’s create two related tables, users and orders, to model a simple e-commerce system. This demonstrates primary keys, foreign keys, and other constraints.

-- First, create the 'users' table
CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Next, create the 'orders' table with a foreign key referencing 'users'
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0),
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),

    -- Define the foreign key relationship
    CONSTRAINT fk_user
        FOREIGN KEY(user_id)
        REFERENCES users(user_id)
        ON DELETE SET NULL -- If a user is deleted, set user_id in orders to NULL
);

Verifying Table Creation

In psql, you can use the \d command to inspect the structure of a table you’ve created.

my_app_db=# \d users

This command will display the columns, their types, modifiers, and any indexes or constraints associated with the users table.

                                     Table "public.users"
   Column   |           Type           | Collation | Nullable |               Default
------------+--------------------------+-----------+----------+--------------------------------------
 user_id    | bigint                   |           | not null | generated always as identity
 username   | text                     |           | not null |
 email      | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()
 updated_at | timestamp with time zone |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
Check constraints:
    "users_email_check" CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'::text)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL

Advanced Concepts

For more complex scenarios, PostgreSQL supports advanced features like:

• Temporary Tables: CREATE TEMP TABLE ... creates a table that is automatically dropped at the end of the current session. Useful for intermediate calculations.
• Table Inheritance: A table can inherit columns from one or more parent tables.
• Partitioning: For very large tables, you can partition them into smaller, more manageable pieces based on a key or range, which can significantly improve query performance.