PostgreSQL Create Table

The CREATE TABLE Statement

Tables are the fundamental storage structure in PostgreSQL. A table is defined by giving it a name and a list of columns, where each column has a data type and, optionally, one or more constraints. The basic syntax looks like this: CREATE TABLE table_name (column1 datatype constraints, column2 datatype constraints, ...);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    signup_date DATE DEFAULT CURRENT_DATE
);

Common Data Types

PostgreSQL supports a wide range of data types. Choosing the right type keeps your data accurate and your storage efficient.

Constraints Keep Data Valid

Constraints are rules enforced on column data. They prevent invalid data from ever being stored, which is far more reliable than validating data only in application code.

• PRIMARY KEY: uniquely identifies each row in the table
• NOT NULL: requires a column to always have a value
• UNIQUE: ensures no two rows share the same value in a column
• DEFAULT: supplies a value automatically when none is given
• FOREIGN KEY: links a column to a primary key in another table
• CHECK: enforces a custom condition on column values

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    quantity INTEGER CHECK (quantity > 0),
    order_total NUMERIC(10, 2) NOT NULL
);

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders';