Extending the model to multiple entity types
Real applications rarely fit into two tables. The sample schema here stores friendship alongside work relations: the knows edge table links people to each other, while works_at connects people to companies.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
-- People (six rows, three cities) CREATE TABLE person ( id int PRIMARY KEY, name text NOT NULL, age int NOT NULL, city text NOT NULL ); INSERT INTO person VALUES (1, 'Alice', 30, 'Berlin'), (2, 'Bob', 25, 'Berlin'), (3, 'Carol', 35, 'Paris'), (4, 'Dan', 28, 'Paris'), (5, 'Eve', 40, 'London'), (6, 'Frank', 33, 'London'); -- "Knows" edges between people (nine rows, two mutual pairs) CREATE TABLE knows ( a int NOT NULL REFERENCES person(id), b int NOT NULL REFERENCES person(id), since int NOT NULL, PRIMARY KEY (a, b) ); INSERT INTO knows VALUES (1, 2, 2018), (2, 1, 2019), (1, 3, 2020), (2, 3, 2020), (3, 2, 2021), (3, 4, 2021), (4, 5, 2022), (5, 6, 2019), (6, 1, 2023); -- Companies (the new vertex type for this post) CREATE TABLE company ( id int PRIMARY KEY, name text NOT NULL, industry text NOT NULL ); INSERT INTO company VALUES (10, 'Acme', 'manufacturing'), (20, 'Globex', 'finance'), (30, 'Initech', 'software'); -- "Works at" edges (the new edge type for this post) CREATE TABLE works_at ( pid int NOT NULL REFERENCES person(id), cid int NOT NULL REFERENCES company(id), role text NOT NULL, since int NOT NULL, PRIMARY KEY (pid, cid) ); INSERT INTO works_at VALUES (1, 10, 'engineer', 2017), -- Alice @ Acme (2, 10, 'engineer', 2019), -- Bob @ Acme (3, 20, 'sales', 2015), -- Carol @ Globex (4, 20, 'engineer', 2020), -- Dan @ Globex (5, 30, 'cto', 2010), -- Eve @ Initech (6, 30, 'engineer', 2022), -- Frank @ Initech (1, 30, 'consultant', 2024); -- Alice ALSO at Initech |
The property graph built on top of this data declares two vertex tables — one for people, one for companies — and exposes a handful of properties for later use.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- The property graph: two vertex labels, two edge labels CREATE PROPERTY GRAPH company_social VERTEX TABLES ( person KEY (id) LABEL person PROPERTIES (id, name, age, city), company KEY (id) LABEL company PROPERTIES (id, name, industry) ) EDGE TABLES ( knows SOURCE KEY (a) REFERENCES person (id) DESTINATION KEY (b) REFERENCES person (id) LABEL knows PROPERTIES (since), works_at SOURCE KEY (pid) REFERENCES person (id) DESTINATION KEY (cid) REFERENCES company (id) LABEL works_at PROPERTIES (role, since) ); |
The interesting part is on the edge side. Because knows joins entries within one table and works_at joins entries across two, the graph spans both tables and mixes private and professional relationships in a single structure.
Traversing a heterogeneous path
With the graph defined, a natural question is where Alice's friends work. The query filters Alice inside GRAPH_TABLE — the alias me, with a WHERE in the MATCH clause — then traverses from person to person through the edges, and from there to companies.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM GRAPH_TABLE (company_social MATCH (me IS person WHERE me.name = 'Alice') -[IS knows]->(friend IS person) -[IS works_at]->(co IS company) COLUMNS (friend.name AS friend, co.name AS company) ) ORDER BY friend, company; friend | company --------+--------- Bob | Acme Carol | Globex (2 rows) |
The equivalent in plain SQL is already non-trivial, which is the point of the exercise:
|
1 2 3 4 5 6 7 |
SELECT pf.name AS friend, c.name AS company FROM person pm JOIN knows k ON k.a = pm.id JOIN person pf ON pf.id = k.b JOIN works_at w ON w.pid = pf.id JOIN company c ON c.id = w.cid WHERE pm.name = 'Alice'; |
Joining graph results back into SQL
Finding two co-workers who know each other would standardly be expressed as multiple comma-separated patterns inside one MATCH. PostgreSQL 19 does not support that syntax yet; the implementation has not caught up with the standard.
|
1 2 3 4 5 |
SELECT * FROM GRAPH_TABLE (company_social MATCH (a IS person)-[IS works_at]->(c IS company) <-[IS works_at]-(b IS person), ... |
The workaround matters more than the original query. Run one GRAPH_TABLE per pattern and join the two results in ordinary SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT m.me, m.via, m.coworker, w.company FROM GRAPH_TABLE (company_social MATCH (a IS person)-[IS knows]->(b IS person)- [IS knows]->(c IS person) WHERE a.id <> c.id COLUMNS (a.id AS aid, c.id AS cid, a.name AS me, b.name AS via, c.name AS coworker) ) m JOIN GRAPH_TABLE (company_social MATCH (x IS person)-[IS works_at]->(co IS company) <-[IS works_at]-(y IS person) WHERE x.id <> y.id COLUMNS (x.id AS xid, y.id AS yid, co.name AS company) ) w ON w.xid = m.aid AND w.yid = m.cid ORDER BY me, coworker; |
|
1 2 3 4 |
me | via | coworker | company -------+-------+----------+--------- Alice | Carol | Bob | Acme Eve | Frank | Alice | Initech |
Because GRAPH_TABLE produces a table, the pattern generalizes: project the join keys out of COLUMNS and the rest of SQL is available as usual.
Pitfalls to plan around
Anonymous edges union across labels
Where a graph has a single edge label, (a)->(b) is unambiguous. In a multi-label graph such as company_social, dropping [IS knows] silently merges every edge table:
|
1 2 3 4 5 6 |
SELECT count(*) FROM GRAPH_TABLE (company_social MATCH (a)->(b) COLUMNS (a.name, b.name)); count ------- 16 |
Nine knows edges and seven works_at edges, combined. The plan shows an Append over two separate join trees, which confirms the union:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
QUERY PLAN ------------------------------------------------------------------------------------------------------ Aggregate (cost=237.71..237.72 rows=1 width=8) -> Append (cost=56.45..229.78 rows=3170 width=0) -> Subquery Scan on unnamed_subquery (cost=56.45..118.03 rows=2040 width=0) -> Hash Join (cost=56.45..97.63 rows=2040 width=64) Hash Cond: (knows.b = person_1.id) -> Hash Join (cost=28.23..64.01 rows=2040 width=4) Hash Cond: (knows.a = person.id) -> Seq Scan on knows (cost=0.00..30.40 rows=2040 width=8) -> Hash (cost=18.10..18.10 rows=810 width=4) -> Seq Scan on person (cost=0.00..18.10 rows=810 width=4) -> Hash (cost=18.10..18.10 rows=810 width=4) -> Seq Scan on person person_1 (cost=0.00..18.10 rows=810 width=4) -> Subquery Scan on unnamed_subquery_1 (cost=57.35..95.90 rows=1130 width=0) -> Hash Join (cost=57.35..84.60 rows=1130 width=64) Hash Cond: (works_at.cid = company.id) -> Hash Join (cost=28.23..52.50 rows=1130 width=4) Hash Cond: (works_at.pid = person_2.id) -> Seq Scan on works_at (cost=0.00..21.30 rows=1130 width=8) -> Hash (cost=18.10..18.10 rows=810 width=4) -> Seq Scan on person person_2 (cost=0.00..18.10 rows=810 width=4) -> Hash (cost=18.50..18.50 rows=850 width=4) -> Seq Scan on company (cost=0.00..18.50 rows=850 width=4) (22 rows) |
Edge labels should be named explicitly in any graph backed by more than one edge table.
No variable-length patterns
Quantified paths such as (a)-[IS knows]->{1,3}(b) are not available yet and remain on the roadmap. Until then, the options are to unroll the hop counts explicitly — one GRAPH_TABLE per count, combined with UNION — or to fall back to WITH RECURSIVE over the underlying edge table.
