PostgreSQL CREATE TABLE Syntax and Examples

CREATE TABLE Syntax

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Explanation:

• table_name: Name of the table to create.
• column1, column2, ...: Names of the columns.
• datatype: Data type for the column (e.g., INTEGER, VARCHAR(50), DATE).
• [constraints]: Optional rules like PRIMARY KEY, NOT NULL, UNIQUE, etc.

Examples

Example 1: Simple Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

• id: Auto-incrementing primary key.
• name: Cannot be null.
• email: Must be unique.
• hire_date: Stores date.

Example 2: Table with Constraints

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• price: Must be greater than 0 (CHECK constraint).
• stock: Defaults to 0 if no value provided.
• created_at: Defaults to the current timestamp.

---

Reference: Official PostgreSQL Documentation: CREATE TABLE