Table Creation in PostgreSQL - Examples

Show Table Definition:

• Show table definition:

\\\\d tablename

• Get extended information:

\\\\d+ tablename

If you've forgotten the table name, type \\\\d in psql to obtain a list of tables and views in the current database.

Create Table from Select:

CREATE TABLE people_over_30 AS SELECT * FROM person WHERE age > 30;

This creates a new table, people_over_30, by selecting rows from the existing person table where age is greater than 30.

Create Unlogged Table:

CREATE UNLOGGED TABLE person (
    person_id BIGINT NOT NULL PRIMARY KEY,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255)
);

Creates an unlogged table named person for potentially faster performance.

Table Creation with Primary Key:

CREATE TABLE person (
    person_id BIGINT NOT NULL PRIMARY KEY,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255)
);

Alternatively, placing the PRIMARY KEY constraint directly in the column definition.

Create a Table that References Another Table:

CREATE TABLE agencies (
    id SERIAL PRIMARY KEY,
    NAME TEXT NOT NULL
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    agency_id INTEGER NOT NULL REFERENCES agencies(id) DEFERRABLE INITIALLY DEFERRED
);

Creates two tables, agencies and users, where the users table has a column referencing the agencies table.