# Some PostgreSQL Database Objects: A Fast Revision Sheet

One schema (`shop`), one tiny e-commerce model, and every object type below used in context. It's meant as a refresher for people who already know SQL, so there's no hand-holding. Tested against PostgreSQL 15+.

---

## 1\. Setup: Schema, Tables, Dummy Data

### Schemas

A schema is a namespace plus a permission boundary. Use one per bounded context, and set `search_path` per role rather than per session.

```sql
CREATE SCHEMA IF NOT EXISTS shop;
SET search_path = shop, public;

-- per-role defaults + least privilege
-- ALTER ROLE app_rw SET search_path = shop;
-- GRANT USAGE ON SCHEMA shop TO app_ro;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA shop GRANT SELECT ON TABLES TO app_ro;
```

### Tables

```sql
CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text        NOT NULL UNIQUE,
    name        text        NOT NULL,
    country     char(2)     NOT NULL,
    referred_by bigint      REFERENCES customers(id) ON DELETE SET NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku    text           NOT NULL UNIQUE,
    name   text           NOT NULL,
    price  numeric(10,2)  NOT NULL CHECK (price > 0),
    cost   numeric(10,2)  NOT NULL CHECK (cost >= 0),
    stock  int            NOT NULL DEFAULT 0 CHECK (stock >= 0),
    margin numeric(10,2)  GENERATED ALWAYS AS (price - cost) STORED,
    CONSTRAINT price_gte_cost CHECK (price >= cost)
);

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 DEFAULT 'new'
                CHECK (status IN ('new','paid','shipped','cancelled')),
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id   bigint        NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint        NOT NULL REFERENCES products(id),
    qty        int           NOT NULL CHECK (qty > 0),
    unit_price numeric(10,2) NOT NULL,
    line_total numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED,
    PRIMARY KEY (order_id, product_id)
);

-- Partitioned table + explicit sequence (see sections below)
CREATE SEQUENCE event_seq START 1000 CACHE 50;

CREATE TABLE order_events (
    id         bigint      NOT NULL DEFAULT nextval('shop.event_seq'),
    order_id   bigint      NOT NULL REFERENCES orders(id),
    event      text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)          -- PK must include the partition key
) PARTITION BY RANGE (created_at);

ALTER SEQUENCE event_seq OWNED BY order_events.id;

CREATE TABLE order_events_2026_10 PARTITION OF order_events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE order_events_2026_11 PARTITION OF order_events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE order_events_default PARTITION OF order_events DEFAULT;

-- Trigger target
CREATE TABLE price_audit (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id bigint        NOT NULL REFERENCES products(id),
    old_price  numeric(10,2) NOT NULL,
    new_price  numeric(10,2) NOT NULL,
    changed_at timestamptz   NOT NULL DEFAULT now()
);

-- Staging table (no WAL)
CREATE UNLOGGED TABLE stg_product_import (
    sku text, name text, price numeric(10,2), cost numeric(10,2), stock int
);
```

### Dummy Data

```sql
INSERT INTO customers (email, name, country, referred_by, created_at) VALUES
 ('alice@example.com','Alice','US',NULL,'2026-09-01'),
 ('bob@example.com',  'Bob',  'DE',1,   '2026-09-10'),
 ('carol@example.com','Carol','BD',2,   '2026-09-20'),
 ('dave@example.com', 'Dave', 'GB',NULL,'2026-10-02');

INSERT INTO products (sku, name, price, cost, stock) VALUES
 ('KB-01','Mechanical Keyboard',120.00, 70.00, 50),
 ('MS-01','Wireless Mouse',       45.00, 20.00, 100),
 ('MN-01','27" Monitor',         320.00,230.00, 20),
 ('HS-01','Headset',              80.00, 40.00, 35);

INSERT INTO orders (customer_id, status, created_at) VALUES
 (1,'shipped','2026-10-05 10:00+00'),
 (2,'paid',   '2026-10-18 14:30+00'),
 (3,'paid',   '2026-11-02 09:15+00'),
 (1,'new',    '2026-11-05 16:45+00'),
 (4,'new',    '2026-09-25 08:00+00');

INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
 (1,1,1,120.00),(1,2,2,45.00),
 (2,3,1,320.00),
 (3,4,2,80.00),(3,2,1,45.00),
 (4,1,1,120.00),
 (5,2,1,45.00);

INSERT INTO order_events (order_id, event, created_at) VALUES
 (1,'created','2026-10-05 10:00+00'),(1,'shipped','2026-10-07 12:00+00'),
 (2,'created','2026-10-18 14:30+00'),(2,'paid','2026-10-18 14:35+00'),
 (3,'created','2026-11-02 09:15+00'),(3,'paid','2026-11-02 09:20+00'),
 (4,'created','2026-11-05 16:45+00');
```

---

## 2\. ER Diagram

Here is our example DDL in ER diagram

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/f89ab253-c4ce-477d-ad76-6bbfdff9c014.png align="center")

Paste into [mermaid.live](https://mermaid.live):

```mermaid
erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    CUSTOMERS |o--o{ CUSTOMERS : refers
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "sold in"
    ORDERS ||--o{ ORDER_EVENTS : logs
    PRODUCTS ||--o{ PRICE_AUDIT : "audited by"

    CUSTOMERS {
        bigint id PK
        text email UK
        text name
        char country
        bigint referred_by FK
        timestamptz created_at
    }
    PRODUCTS {
        bigint id PK
        text sku UK
        text name
        numeric price "CHECK > 0"
        numeric cost
        int stock "CHECK >= 0"
        numeric margin "GENERATED STORED"
    }
    ORDERS {
        bigint id PK
        bigint customer_id FK
        text status "CHECK enum"
        timestamptz created_at
    }
    ORDER_ITEMS {
        bigint order_id PK, FK
        bigint product_id PK, FK
        int qty "CHECK > 0"
        numeric unit_price
        numeric line_total "GENERATED STORED"
    }
    ORDER_EVENTS {
        bigint id PK "event_seq"
        timestamptz created_at PK "PARTITION KEY"
        bigint order_id FK
        text event
    }
    PRICE_AUDIT {
        bigint id PK
        bigint product_id FK
        numeric old_price
        numeric new_price
        timestamptz changed_at
    }
    STG_PRODUCT_IMPORT {
        text sku
        text name
        numeric price
        numeric cost
        int stock
    }
```

---

## 3\. The Objects

### Views

A stored query with no data of its own. Simple views are auto-updatable, and `security_barrier` matters if you use them for row filtering.

```sql
CREATE VIEW v_order_totals AS
SELECT o.id AS order_id, o.customer_id, c.email, o.status, o.created_at,
       sum(oi.line_total) AS total
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.email;
```

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/cba97b7e-2bfe-4543-8969-56201e6741b6.png align="center")

### Materialized Views and REFRESH

A materialized view stores the result physically and is indexable, but it goes stale until you refresh it. `CONCURRENTLY` avoids blocking readers, but it **requires a unique index** and an already-populated matview.

```sql
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT o.created_at::date AS day,
       count(DISTINCT o.id) AS orders,
       sum(oi.line_total)   AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid','shipped')
GROUP BY 1;

CREATE UNIQUE INDEX ON mv_daily_revenue (day);

REFRESH MATERIALIZED VIEW mv_daily_revenue;               -- ACCESS EXCLUSIVE lock
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;  -- diff-based, readers keep working
```

> Postgres has no incremental refresh and no built-in scheduler. Use `pg_cron` or your orchestrator.

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/466c4cf9-3e45-45fc-850f-7d699d2e2254.png align="center")

### Functions

Functions return a value, run inside the caller's transaction, and can be used in queries. Declare volatility honestly (`IMMUTABLE`/`STABLE`/`VOLATILE`) because the planner relies on it. Simple `LANGUAGE sql` functions can be inlined.

```sql
CREATE FUNCTION fn_customer_ltv(p_customer_id bigint)
RETURNS numeric
LANGUAGE sql STABLE AS $$
    SELECT coalesce(sum(total), 0)
    FROM v_order_totals
    WHERE customer_id = p_customer_id AND status IN ('paid','shipped');
$$;

SELECT name, fn_customer_ltv(id) FROM customers;
/*
name |fn_customer_ltv|
-----+---------------+
Alice|         210.00|
Bob  |         320.00|
Carol|         205.00|
Dave |              0|
*/
```

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/283f3414-572f-4964-9d2e-d30a71aa3afd.png align="center")

### Procedures

Procedures are invoked with `CALL`, return no value, and **can control transactions** (`COMMIT`/`ROLLBACK`), which functions cannot.

```sql
CREATE PROCEDURE sp_place_order(p_customer bigint, p_product bigint, p_qty int)
LANGUAGE plpgsql AS $$
DECLARE
    v_price numeric;
    v_order bigint;
BEGIN
    UPDATE products SET stock = stock - p_qty   -- CHECK (stock >= 0) guards oversell
    WHERE id = p_product
    RETURNING price INTO v_price;

    IF NOT FOUND THEN RAISE EXCEPTION 'unknown product %', p_product; END IF;

    INSERT INTO orders (customer_id) VALUES (p_customer) RETURNING id INTO v_order;
    INSERT INTO order_items VALUES (v_order, p_product, p_qty, v_price);
    INSERT INTO order_events (order_id, event) VALUES (v_order, 'created');
    COMMIT;
END $$;

CALL sp_place_order(2, 4, 1);
```

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/6583b743-f6e5-46ff-aec6-838dd5abd4e8.png align="center")

### Temporary Tables

Temp tables are session-scoped and unlogged. They are invisible to other sessions and can be analyzed and indexed. `ON COMMIT DROP` keeps things clean.

```sql
BEGIN;
CREATE TEMP TABLE tmp_big_orders ON COMMIT DROP AS
    SELECT order_id, total FROM v_order_totals WHERE total > 100;
ANALYZE tmp_big_orders;                    -- autovacuum can't see temp tables
SELECT * FROM tmp_big_orders ORDER BY total DESC;
COMMIT;
```

### Unlogged Tables

An unlogged table skips WAL, so writes are much faster. The table is **truncated after a crash and not replicated**. It suits staging and ETL landing zones, never source-of-truth data.

```sql
COPY stg_product_import FROM '/tmp/products.csv' CSV HEADER;  -- or INSERT ... 

INSERT INTO products (sku, name, price, cost, stock)
SELECT sku, name, price, cost, stock FROM stg_product_import
ON CONFLICT (sku) DO UPDATE
   SET price = EXCLUDED.price, cost = EXCLUDED.cost, stock = EXCLUDED.stock;

TRUNCATE stg_product_import;
-- ALTER TABLE stg_product_import SET LOGGED;   -- flip when needed (rewrites table)
```

### Sequences

Sequences are non-transactional, so rollbacks leave **gaps**, and `CACHE` can skip values across sessions. `GENERATED ... AS IDENTITY` is the SQL-standard replacement for `serial`, and `event_seq` above is the explicit variant.

```sql
SELECT nextval('event_seq'), currval('event_seq');
SELECT setval('event_seq', (SELECT max(id) FROM order_events));  -- resync after bulk loads
```

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/fd9f461e-34d1-4f1c-91f3-a7e6ad49d2d4.png align="center")

### Generated Columns

`STORED` generated columns are computed on write and can be indexed. Postgres 18 adds `VIRTUAL`. They can't reference other generated columns or non-immutable functions.

```sql
SELECT sku, price, cost, margin FROM products;   -- margin = price - cost
-- UPDATE products SET margin = 1;               -- ERROR: can only be updated to DEFAULT
CREATE INDEX ON order_items (line_total);
```

### Table Partitioning

Declarative partitioning supports range, list, and hash. The planner uses **partition pruning**, so always filter on the key. Primary and unique keys must include the partition key. Retention becomes `DETACH` plus `DROP` instead of a giant `DELETE`.

```sql
EXPLAIN SELECT * FROM order_events
WHERE created_at >= '2026-11-01' AND created_at < '2026-12-01';  -- scans only _2026_11

-- Add the next partition ahead of time
CREATE TABLE order_events_2026_12 PARTITION OF order_events
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- Retention
ALTER TABLE order_events DETACH PARTITION order_events_2026_10 CONCURRENTLY;
```

> A `DEFAULT` partition is a safety net, but it makes adding new partitions slower because it must be scanned for conflicting rows.

### Declarative Constraints: CHECK, UNIQUE, FOREIGN KEY

Everything is already in the DDL above. A few things worth remembering:

```sql
-- CHECK: NULL passes (the condition is "not false"), so pair with NOT NULL
-- UNIQUE: NULLs are distinct by default; PG15+ can change that
ALTER TABLE customers ADD CONSTRAINT uq_email_per_country
    UNIQUE NULLS NOT DISTINCT (email, country);

-- Add constraints to big tables without a long lock
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id)
    REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_cust;   -- only needs SHARE UPDATE EXCLUSIVE

-- FK columns are NOT auto-indexed. Index them yourself
CREATE INDEX ON orders (customer_id);
```

### Triggers

Triggers are row- or statement-level and fire BEFORE/AFTER/INSTEAD OF. A `WHEN` clause and `UPDATE OF col` keep them cheap.

```sql
CREATE FUNCTION trg_price_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO price_audit (product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
    RETURN NEW;
END $$;

CREATE TRIGGER products_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION trg_price_audit();

UPDATE products SET price = 130 WHERE sku = 'KB-01';
SELECT * FROM price_audit;
/*
id|product_id|old_price|new_price|changed_at                   |
--+----------+---------+---------+-----------------------------+
 1|         1|   120.00|   130.00|2026-11-15 12:27:28.297 +0600|
*/
```

> Triggers are invisible side effects. Use them for audit and invariants, not for business workflows.

### Foreign Data Wrappers

FDWs let you query remote sources as local tables. `postgres_fdw` pushes down filters, joins, and aggregates where it can. Check with `EXPLAIN VERBOSE`.

```sql
CREATE EXTENSION postgres_fdw;

CREATE SERVER crm_srv FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'crm.internal', dbname 'crm', port '5432');

CREATE USER MAPPING FOR CURRENT_USER SERVER crm_srv
    OPTIONS (user 'reader', password '***');

CREATE FOREIGN TABLE crm_leads (lead_id bigint, email text, source text)
    SERVER crm_srv OPTIONS (schema_name 'public', table_name 'leads');

-- or import many at once
-- IMPORT FOREIGN SCHEMA public LIMIT TO (leads) FROM SERVER crm_srv INTO shop;

SELECT l.source, count(*)
FROM crm_leads l JOIN customers c USING (email)
GROUP BY 1;
```

### Common Table Expressions

Since PG12, non-recursive CTEs referenced once are **inlined**. Use `MATERIALIZED` or `NOT MATERIALIZED` to override that. Recursive CTEs handle trees and graphs, and CTEs can also contain data-modifying statements.

```sql
-- Recursive: full referral chain
WITH RECURSIVE chain AS (
    SELECT id, name, referred_by, 1 AS depth
    FROM customers WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.referred_by, ch.depth + 1
    FROM customers c JOIN chain ch ON c.referred_by = ch.id
)
SELECT * FROM chain;
/*
id|name |referred_by|depth|
--+-----+-----------+-----+
 1|Alice|           |    1|
 2|Bob  |          1|    2|
 3|Carol|          2|    3|
*/

-- Writable CTE: cancel stale orders and log it atomically
WITH cancelled AS (
    UPDATE orders SET status = 'cancelled'
    WHERE status = 'new' AND created_at < now() - interval '30 days'
    RETURNING id
)
INSERT INTO order_events (order_id, event)
SELECT id, 'auto_cancelled' FROM cancelled;

-- Force a single evaluation (optimization fence)
WITH big AS MATERIALIZED (SELECT * FROM v_order_totals WHERE total > 100)
SELECT count(*) FROM big;
/*
count|
-----+
    4|
*/
```

---

## 4\. Takeaways

| Need | Reach for |
| --- | --- |
| Reusable query, always fresh | **View** |
| Expensive aggregate, tolerates staleness | **Materialized view** + `REFRESH ... CONCURRENTLY` |
| Logic in a query | **Function** |
| Multi-step logic with transaction control | **Procedure** |
| Fast scratch space, per session | **Temp table** |
| Fast ETL landing, loss-tolerant | **Unlogged table** |
| Derived values, always consistent | **Generated column** |
| Time-series data and cheap retention | **Partitioning** |
| Correctness enforced by the DB | **Constraints** |
| Audit and invariants | **Triggers** |
| Cross-database queries without ETL | **FDW** |
| Readable steps, trees, writable pipelines | **CTEs** |
