Creating and Managing Tables

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    email       text UNIQUE NOT NULL,
    full_name   text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status      text   NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
    total_cents integer NOT NULL CHECK (total_cents >= 0),
    placed_at   timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT orders_unique_ref UNIQUE (customer_id, placed_at)
);

CREATE TEMP TABLE staging_orders AS
SELECT * FROM orders WHERE status = 'new';

CREATE TEMP TABLE t (id int) ON COMMIT DROP;

CREATE UNLOGGED TABLE cache_entries (
    key text PRIMARY KEY,
    value jsonb,
    expires_at timestamptz
);

CREATE TABLE big_customers AS
SELECT * FROM customers WHERE lifetime_value > 10000;

CREATE TABLE empty_copy AS SELECT * FROM customers WITH NO DATA;

CREATE TABLE customers_archive (
    LIKE customers INCLUDING ALL EXCLUDING INDEXES
);

\dt
\dt+ app.*
SELECT table_schema, table_name
  FROM information_schema.tables
 WHERE table_type='BASE TABLE' AND table_schema NOT IN ('pg_catalog','information_schema');

SELECT column_name, data_type, is_nullable, column_default
  FROM information_schema.columns
 WHERE table_schema='app' AND table_name='customers'
 ORDER BY ordinal_position;

ALTER TABLE customers RENAME TO customer;
ALTER TABLE app.customers RENAME COLUMN email_address TO email;

ALTER TABLE customers OWNER TO app_owner;

CREATE TABLE backup AS TABLE customers;            -- shorthand
CREATE TABLE clone (LIKE customers INCLUDING ALL);
INSERT INTO clone SELECT * FROM customers;

TRUNCATE TABLE staging;
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

DROP TABLE old_orders;
DROP TABLE IF EXISTS old_orders CASCADE;