Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- schema: tables and indexes
CREATE TABLE profile (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , email TEXT NOT NULL UNIQUE
  , name TEXT NOT NULL
  , type TEXT NOT NULL CHECK (type IN ('client', 'manager', 'admin'))
  , client_id BIGINT UNIQUE GENERATED ALWAYS AS (CASE WHEN type = 'client' THEN id END) STORED
  , manager_id BIGINT UNIQUE GENERATED ALWAYS AS (CASE WHEN type = 'manager' THEN id END) STORED
  , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE profile_detail (
  profile_id BIGINT PRIMARY KEY REFERENCES profile (id)
  , bio TEXT
  , phone TEXT
);

CREATE TABLE address (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , profile_id BIGINT NOT NULL REFERENCES profile (id)
  , city TEXT NOT NULL
  , street TEXT NOT NULL
  , UNIQUE (id, profile_id)
);

CREATE INDEX address_profile_id_idx ON address (profile_id);

CREATE TABLE item (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , parent_item_id BIGINT REFERENCES item (id)
  , sku TEXT NOT NULL UNIQUE
  , name TEXT NOT NULL
  , price NUMERIC(12, 2) NOT NULL DEFAULT 0
);

CREATE INDEX item_parent_item_id_idx ON item (parent_item_id);

CREATE TABLE document (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  , client_id BIGINT NOT NULL REFERENCES profile (client_id)
  , manager_id BIGINT REFERENCES profile (manager_id)
  , delivery_address_id BIGINT
  , doc_number TEXT NOT NULL UNIQUE
  , issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  , CONSTRAINT document_delivery_address_id_client_id_fkey
    FOREIGN KEY (delivery_address_id, client_id) REFERENCES address (id, profile_id)
);

CREATE INDEX document_client_id_idx ON document (client_id);
CREATE INDEX document_manager_id_idx ON document (manager_id);
CREATE INDEX document_delivery_address_id_idx ON document (delivery_address_id);

CREATE TABLE document_item (
  document_id BIGINT NOT NULL REFERENCES document (id)
  , item_id BIGINT NOT NULL REFERENCES item (id)
  , quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0)
  , PRIMARY KEY (document_id, item_id)
);

CREATE INDEX document_item_item_id_idx ON document_item (item_id);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE TABLE



CREATE INDEX


-- demo data
INSERT INTO profile (email, name, type) VALUES
  ('c1@x.io', 'Client One', 'client')
  , ('c2@x.io', 'Client Two', 'client')
  , ('m@x.io', 'Manager', 'manager')
  , ('c3@x.io', 'Client Three', 'client')
;

INSERT INTO profile_detail (profile_id, bio, phone) VALUES
  (1, 'Regular buyer', '+49 30 1111111')
  , (3, 'Sales manager', '+49 30 5555555')
;

INSERT INTO address (profile_id, city, street) VALUES
  (1, 'Berlin', 'Alexanderplatz 1')
  , (2, 'Paris', 'Rue de Rivoli 2')
;

INSERT INTO item (parent_item_id, sku, name, price) VALUES
  (NULL, 'SKU-1', 'Laptop', 1200)
  , (1, 'SKU-2', 'Mouse', 25)
  , (1, 'SKU-3', 'Keyboard', 90)
  , (2, 'SKU-4', 'Mouse Pro', 45)
;

INSERT INTO document (client_id, manager_id, delivery_address_id, doc_number, issued_at) VALUES
  (1, 3, 1, 'DOC-1', '2026-07-01')
  , (1, NULL, NULL, 'DOC-2', '2026-07-15')
  , (2, 3, 2, 'DOC-3', '2026-07-20')
;

INSERT INTO document_item (document_id, item_id, quantity) VALUES
  (1, 1, 1)
  , (1, 2, 2)
  , (2, 3, 1)
  , (3, 2, 5)
;

INSERT 0 4



INSERT 0 2



INSERT 0 2



INSERT 0 4



INSERT 0 3



INSERT 0 4


-- relation functions, one pair per foreign key
CREATE OR REPLACE FUNCTION public.address_list(profile)
 RETURNS SETOF address
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.address WHERE (profile_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.client(document)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (client_id) = (($1).client_id)$function$
;
CREATE OR REPLACE FUNCTION public.client_document_list(profile)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (client_id) = (($1).client_id)$function$
;
CREATE OR REPLACE FUNCTION public.delivery_address(document)
 RETURNS SETOF address
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.address WHERE (id, profile_id) = (($1).delivery_address_id, ($1).client_id)$function$
;
CREATE OR REPLACE FUNCTION public.document(document_item)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (id) = (($1).document_id)$function$
;
CREATE OR REPLACE FUNCTION public.document_item_list(document)
 RETURNS SETOF document_item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document_item WHERE (document_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.document_item_list(item)
 RETURNS SETOF document_item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document_item WHERE (item_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.document_list(address)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (delivery_address_id, client_id) = (($1).id, ($1).profile_id)$function$
;
CREATE OR REPLACE FUNCTION public.item(document_item)
 RETURNS SETOF item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.item WHERE (id) = (($1).item_id)$function$
;
CREATE OR REPLACE FUNCTION public.item_list(item)
 RETURNS SETOF item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.item WHERE (parent_item_id) = (($1).id)$function$
;
CREATE OR REPLACE FUNCTION public.manager(document)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (manager_id) = (($1).manager_id)$function$
;
CREATE OR REPLACE FUNCTION public.manager_document_list(profile)
 RETURNS SETOF document
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.document WHERE (manager_id) = (($1).manager_id)$function$
;
CREATE OR REPLACE FUNCTION public.parent_item(item)
 RETURNS SETOF item
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.item WHERE (id) = (($1).parent_item_id)$function$
;
CREATE OR REPLACE FUNCTION public.profile(profile_detail)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (id) = (($1).profile_id)$function$
;
CREATE OR REPLACE FUNCTION public.profile(address)
 RETURNS SETOF profile
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile WHERE (id) = (($1).profile_id)$function$
;
CREATE OR REPLACE FUNCTION public.profile_detail(profile)
 RETURNS SETOF profile_detail
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$SELECT * FROM public.profile_detail WHERE (profile_id) = (($1).id)$function$
;

CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


-- schema, data and relation functions are hidden above: "show hidden batches"

-- document lines: five relations, both directions, zero ON
SELECT client.name, profile_detail.phone, delivery_address.city, item.name, quantity
FROM document
, client(document)
, profile_detail(client)
, delivery_address(document)
, document_item_list(document)
, item(document_item_list)
WHERE document.doc_number = 'DOC-1';


name
phone
city
name
quantity


Client One
+49 30 1111111
Berlin
Laptop
1

Client One
+49 30 1111111
Berlin
Mouse
2



SELECT 2


-- and the query it turns into, node for node
EXPLAIN 
SELECT client.name, profile_detail.phone, delivery_address.city, item.name, quantity
FROM document
, client(document)
, profile_detail(client)
, delivery_address(document)
, document_item_list(document)
, item(document_item_list)
WHERE document.doc_number = 'DOC-1'
;
EXPLAIN
SELECT p.name, pd.phone, a.city, i.name, di.quantity
FROM document d
JOIN profile p ON p.client_id = d.client_id
JOIN profile_detail pd ON pd.profile_id = p.id
JOIN address a ON (a.id, a.profile_id) = (d.delivery_address_id, d.client_id)
JOIN document_item di ON di.document_id = d.id
JOIN item i ON i.id = di.item_id
WHERE d.doc_number = 'DOC-1'


QUERY PLAN


Nested Loop  (cost=4.96..31.97 rows=4 width=132)

  ->  Nested Loop  (cost=4.81..31.45 rows=2 width=108)

        ->  Nested Loop  (cost=4.66..31.05 rows=2 width=84)

              ->  Nested Loop  (cost=0.45..16.60 rows=1 width=80)

                    Join Filter: (document.client_id = profile.client_id)

                    ->  Nested Loop  (cost=0.30..16.36 rows=1 width=56)

                          ->  Index Scan using document_doc_number_key on document  (cost=0.15..8.17 rows=1 width=24)

                                Index Cond: (doc_number = 'DOC-1'::text)

                          ->  Index Scan using address_id_profile_id_key on address  (cost=0.15..8.17 rows=1 width=48)

                                Index Cond: ((id = document.delivery_address_id) AND (profile_id = document.client_id))

                    ->  Index Scan using profile_client_id_key on profile  (cost=0.15..0.23 rows=1 width=48)

                          Index Cond: (client_id = address.profile_id)

              ->  Bitmap Heap Scan on document_item  (cost=4.21..14.37 rows=8 width=20)

                    Recheck Cond: (document.id = document_id)

                    ->  Bitmap Index Scan on document_item_pkey  (cost=0.00..4.21 rows=8 width=0)

                          Index Cond: (document_id = document.id)

        ->  Index Scan using item_pkey on item  (cost=0.15..0.20 rows=1 width=40)

              Index Cond: (id = document_item.item_id)

  ->  Index Scan using profile_detail_pkey on profile_detail  (cost=0.15..0.26 rows=1 width=40)

        Index Cond: (profile_id = profile.id)



EXPLAIN




QUERY PLAN


Nested Loop  (cost=4.96..31.97 rows=4 width=132)

  ->  Nested Loop  (cost=4.81..31.45 rows=2 width=108)

        ->  Nested Loop  (cost=4.66..31.05 rows=2 width=84)

              ->  Nested Loop  (cost=0.45..16.60 rows=1 width=80)

                    Join Filter: (d.client_id = p.client_id)

                    ->  Nested Loop  (cost=0.30..16.36 rows=1 width=56)

                          ->  Index Scan using document_doc_number_key on document d  (cost=0.15..8.17 rows=1 width=24)

                                Index Cond: (doc_number = 'DOC-1'::text)

                          ->  Index Scan using address_id_profile_id_key on address a  (cost=0.15..8.17 rows=1 width=48)

                                Index Cond: ((id = d.delivery_address_id) AND (profile_id = d.client_id))

                    ->  Index Scan using profile_client_id_key on profile p  (cost=0.15..0.23 rows=1 width=48)

                          Index Cond: (client_id = a.profile_id)

              ->  Bitmap Heap Scan on document_item di  (cost=4.21..14.37 rows=8 width=20)

                    Recheck Cond: (d.id = document_id)

                    ->  Bitmap Index Scan on document_item_pkey  (cost=0.00..4.21 rows=8 width=0)

                          Index Cond: (document_id = d.id)

        ->  Index Scan using item_pkey on item i  (cost=0.15..0.20 rows=1 width=40)

              Index Cond: (id = di.item_id)

  ->  Index Scan using profile_detail_pkey on profile_detail pd  (cost=0.15..0.26 rows=1 width=40)

        Index Cond: (profile_id = p.id)



EXPLAIN


-- optional manager: LEFT JOIN ... ON true
SELECT d.doc_number, p.name client_name, m.name manager_name
FROM document d, client(d) p
LEFT JOIN manager(d) m ON true;


doc_number
client_name
manager_name


DOC-1
Client One
Manager

DOC-2
Client One
null

DOC-3
Client Two
Manager



SELECT 3


-- composite FK stays hidden
SELECT d.doc_number, a.city, a.street
FROM document d, delivery_address(d) a;


doc_number
city
street


DOC-1
Berlin
Alexanderplatz 1

DOC-3
Paris
Rue de Rivoli 2



SELECT 2


-- the whole related row as a field
SELECT doc_number, (document.client).* FROM document;


doc_number
id
email
name
type
client_id
manager_id
created_at


DOC-1
1
c1@x.io
Client One
client
1
null
2026-08-07 13:36:13.28402+00

DOC-2
1
c1@x.io
Client One
client
1
null
2026-08-07 13:36:13.28402+00

DOC-3
2
c2@x.io
Client Two
client
2
null
2026-08-07 13:36:13.28402+00



SELECT 3


-- clients without documents
SELECT name FROM profile
WHERE type = 'client' AND NOT EXISTS (SELECT FROM client_document_list(profile));


name


Client Three



SELECT 1
name	phone	city	name	quantity
Client One	+49 30 1111111	Berlin	Laptop	1
Client One	+49 30 1111111	Berlin	Mouse	2
doc_number	client_name	manager_name
DOC-1	Client One	Manager
DOC-2	Client One	null
DOC-3	Client Two	Manager
doc_number	id	email	name	type	client_id	manager_id	created_at
DOC-1	1	c1@x.io	Client One	client	1	null	2026-08-07 13:36:13.28402+00
DOC-2	1	c1@x.io	Client One	client	1	null	2026-08-07 13:36:13.28402+00
DOC-3	2	c2@x.io	Client Two	client	2	null	2026-08-07 13:36:13.28402+00