PostgreSQL Fundamentals

What we're doing: Show PostgreSQL refusing writes that violate declared structure, rather than silently storing whatever arrives.

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total NUMERIC(10, 2) NOT NULL CHECK (total >= 0)
);

INSERT INTO customers (email) VALUES ('a@example.com');

INSERT INTO orders (customer_id, total) VALUES (999, 50.00);

1–4

Declares the shape once: id, and an email that must be present and unique.

6–10

orders declares its own rules, including a foreign key back to customers and a CHECK that total cannot be negative.

12

A valid customer row — passes NOT NULL and UNIQUE.

14

customer_id 999 does not exist in customers yet, so the foreign key constraint rejects this row outright.

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

Why this works: PostgreSQL checks every constraint declared on a table before a row is allowed to exist — types, NOT NULL, UNIQUE, CHECK and foreign keys alike. The order INSERT never reaches the table; the server rejects it at the boundary. A document store with no schema validation would have stored this row as-is, leaving the application to notice the dangling reference later, if it ever does.

What we're doing: Show the containment chain and two terms that sit outside it — a role and an extension — in one connected script.

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    total NUMERIC(10, 2) NOT NULL
);

INSERT INTO sales.orders (total) VALUES (250.00), (1500.00), (75.00);

CREATE VIEW sales.big_orders AS
    SELECT * FROM sales.orders WHERE total > 1000;

SELECT * FROM sales.big_orders;

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'sales';

1

Creates a schema — a namespace, not yet holding any objects.

3–6

The table lives inside sales — its full name is sales.orders.

8

Three rows go in; id fills itself from the identity column's underlying sequence.

10–11

A view: a saved SELECT. big_orders is not a copy of the data — it recomputes the filter on every read.

13

Querying the view reads live from orders through the saved query.

15–17

Both the table and the view show up here — information_schema.tables lists views alongside base tables, and table_schema names the schema explicitly for each.

id | total
----+---------
  2 | 1500.00
(1 row)

table_schema | table_name
--------------+------------
sales         | orders
sales         | big_orders
(2 rows)

Why this works: sales.orders demonstrates schema containing table; id, total and each inserted row demonstrate column and row. big_orders is a view — the same three rows are not duplicated anywhere, the SELECT just runs again each time the view is queried, which is why it only ever shows what currently matches the filter. The information_schema query returns BOTH sales.orders and sales.big_orders — information_schema.tables lists views alongside base tables — which is itself proof that schema is a real, queryable concept in PostgreSQL, not just a naming convention.

What we're doing: Show that a connection to one database cannot query a table in a sibling database on the same server, and that schemas inside one database are reachable without reconnecting.

-- Run from a connection to database "storefront"
CREATE SCHEMA reporting;
CREATE TABLE reporting.daily_totals (day DATE, total NUMERIC);

-- Reachable: a different schema, same database, same connection
SELECT count(*) FROM reporting.daily_totals;

-- Not reachable from this connection at all: a table in database "analytics",
-- named the full three-part way (database.schema.table)
SELECT count(*) FROM analytics.public.events;

2

A new schema inside the current database — no new connection needed.

6

Cross-schema, same-database access works over the one open connection.

10

analytics is a different database. Naming it explicitly with a three-part reference is what surfaces PostgreSQL's actual error — this connection cannot be redirected mid-session to look there.

ERROR:  cross-database references are not implemented: "analytics.public.events"

Why this works: PostgreSQL enforces database isolation at the connection level: a session authenticates against one database name and stays scoped to it for its whole lifetime. Schemas are a lighter-weight namespace INSIDE that one database, so reporting.daily_totals is one query away. A two-part name like analytics.events does not even get this far — since analytics is not a schema in the current database, PostgreSQL reports "relation does not exist" instead, which is a different (and more confusing) error. Writing the full three-part database.schema.table form is what surfaces the real reason: there is no SQL syntax that crosses the database boundary the way schema-qualifying a table name crosses a schema boundary.