How to CREATE TABLE in PostgreSQL

How do I use CREATE TABLE in PostgreSQL?

CREATE TABLE defines a new table with columns, data types, constraints, defaults, and optional storage parameters.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CREATE TABLE do in PostgreSQL?

CREATE TABLE builds a brand-new, empty table in the current schema. You declare each column, its data type, optional default value, and any constraints such as PRIMARY KEY or UNIQUE.

How do I create a simple table?

Supply the table name and a comma-separated list of column definitions.PostgreSQL will allocate storage and record metadata instantly.

Example: Customers table

CREATE TABLE Customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT NOW() );

How do I add constraints and defaults?

Append PRIMARY KEY, UNIQUE, CHECK, REFERENCES, or DEFAULT clauses after each column or as table-level constraints for multiple columns.

Example: composite key

CREATE TABLE OrderItems ( order_id INT, product_id INT, quantity INT CHECK (quantity > 0), PRIMARY KEY (order_id, product_id) );

When should I use TEMPORARY or IF NOT EXISTS?

TEMPORARY tables vanish at session end—ideal for staging.IF NOT EXISTS prevents errors when the table already exists, making scripts idempotent.

How do I control table storage?

Use WITH (autovacuum_enabled=false) or TABLESPACE to fine-tune performance and disk layout. Only advanced users normally need these options.

Best practices

• Always set a primary key.
• Use appropriate data types (e.g., NUMERIC for money).
• Document defaults to avoid confusion.
• Keep table and column names lowercase with underscores.

Common mistakes

See below for quick fixes to frequent errors.

Why How to CREATE TABLE in PostgreSQL is important

How to CREATE TABLE in PostgreSQL Example Usage


-- Orders table with foreign key and default timestamp
CREATE TABLE Orders (
    id            SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES Customers(id),
    order_date    TIMESTAMPTZ DEFAULT NOW(),
    total_amount  NUMERIC(10,2) NOT NULL
);

How to CREATE TABLE in PostgreSQL Syntax


CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] table_name (
    column_name data_type [COLLATE collation] [column_constraint ...],
    table_constraint ...
) [INHERITS (parent_table,...)]
[PARTITION BY { RANGE | LIST | HASH } (partition_key)]
[WITH (storage_parameter = value, ...)]
[ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }]
[TABLESPACE tablespace_name];

Common Mistakes

Forgetting a primary key: Omitting PRIMARY KEY makes updates and joins slower. Always declare a SERIAL/BIGSERIAL or UUID column as PRIMARY KEY.
Using VARCHAR without length: Older habits from MySQL lead developers to write VARCHAR(255). In PostgreSQL, TEXT or VARCHAR without length is simpler and just as efficient.