PostgreSQL 19 introduces SQL/PGQ, the ISO/IEC 9075-16 (2023) syntax for querying graphs that live in regular relational tables. No extension is required and no data is copied: a property graph is metadata defined on top of the tables you already have, and graph queries are expressed against it. Two constructs do the work — CREATE PROPERTY GRAPH declares the graph, and GRAPH_TABLE queries it inside ordinary SQL statements.
Declaring the graph
The smallest useful example is a social network with two tables: a person table and a knows table mapping who knows whom.
|
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 |
CREATE TABLE person ( id int PRIMARY KEY, name text NOT NULL, age int NOT NULL, city text NOT NULL ); 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 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'); 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); |
A property graph is defined as follows.
|
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 |
friends=# \h CREATE PROPERTY GRAPH Command: CREATE PROPERTY GRAPH Description: define a new SQL-property graph Syntax: CREATE [ TEMP | TEMPORARY ] PROPERTY GRAPH name [ {VERTEX|NODE} TABLES ( vertex_table_definition [, ...] ) ] [ {EDGE|RELATIONSHIP} TABLES ( edge_table_definition [, ...] ) ] where vertex_table_definition is: vertex_table_name [ AS alias ] [ KEY ( column_name [, ...] ) ] [ element_table_label_and_properties ] and edge_table_definition is: edge_table_name [ AS alias ] [ KEY ( column_name [, ...] ) ] SOURCE [ KEY ( column_name [, ...] ) REFERENCES ] source_table [ ( column_name [, ...] ) ] DESTINATION [ KEY ( column_name [, ...] ) REFERENCES ] dest_table [ ( column_name [, ...] ) ] [ element_table_label_and_properties ] and element_table_label_and_properties is either: NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) or: { { LABEL label_name | DEFAULT LABEL } [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) ] } [...] URL: https://www.postgresql.org/docs/devel/sql-create-property-graph.html |
Read the definition as a contract. Rows in person are labelled person — the graph label can differ from the underlying table name. Such nodes are called vertices, they are connected by edges, and they expose properties that in this example are simply columns. The edge definition describes how vertices link to one another.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROPERTY GRAPH social VERTEX TABLES ( person KEY (id) LABEL person PROPERTIES (id, name, age, city) ) EDGE TABLES ( knows SOURCE KEY (a) REFERENCES person (id) DESTINATION KEY (b) REFERENCES person (id) LABEL knows PROPERTIES (since) ); |
Querying vertices and walking edges
The simplest possible graph query lists people.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
tutorial=# SELECT name FROM GRAPH_TABLE (social MATCH (p IS person) COLUMNS (p.name) ) ORDER BY name; name ------- Alice Bob Carol Dan Eve Frank (6 rows) |
It is logically equivalent to a plain relational projection.
|
1 |
SELECT name FROM person ORDER BY name |
GRAPH_TABLE names the graph to use. The person vertex is bound to the alias p, and its name column is projected.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
tutorial=# SELECT * FROM GRAPH_TABLE (social MATCH (p IS person ) COLUMNS (p.id, p.name) ); id | name ----+------- 1 | Alice 2 | Bob 3 | Carol 4 | Dan 5 | Eve 6 | Frank (6 rows) tutorial=# SELECT * FROM GRAPH_TABLE (social MATCH (p IS person ) COLUMNS (p.*) ); ERROR: "*" is not supported here LINE 1: ...OM GRAPH_TABLE (social MATCH (p IS person ) COLUMNS (p.*) ); |
Multiple columns are allowed in the COLUMNS clause, but * is not — the column list must be written out explicitly.
Answering "who knows whom?" requires walking from one vertex to another along an edge. A graph query walks vertices and edges: start at a person, follow the edges defined against the knows table (an edge of the graph) to the other person.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
tutorial=# SELECT * FROM GRAPH_TABLE (social MATCH (p IS person )-[IS knows]->(p2 IS person) COLUMNS (p.id, p.name, p2.id, p2.name) ) ORDER BY 1, 2, 3; id | name | id | name ----+-------+----+------- 1 | Alice | 2 | Bob 1 | Alice | 3 | Carol 2 | Bob | 1 | Alice 2 | Bob | 3 | Carol 3 | Carol | 2 | Bob 3 | Carol | 4 | Dan 4 | Dan | 5 | Eve 5 | Eve | 6 | Frank 6 | Frank | 1 | Alice (9 rows) |
The pattern (a)-[IS knows]->(b) is an edge pattern. The arrow -> follows the edge in the direction it was declared (SOURCE → DESTINATION); <- follows it in reverse. A bare - is undirected and traverses both directions, so rows are listed twice.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM GRAPH_TABLE (social MATCH (p IS person )-[IS knows]-(p2 IS person) COLUMNS (p.id, p.name, p2.id, p2.name) ) ORDER BY 1, 2, 3; id | name | id | name ----+-------+----+------- 1 | Alice | 2 | Bob 1 | Alice | 2 | Bob 1 | Alice | 3 | Carol … (18 rows) |
The undirected operator compiles to an OR clause, which is why the row count comes out wrong.
|
1 2 3 |
(person.id = knows.a AND person_1.id = knows.b) OR (person_1.id = knows.a AND person.id = knows.b) |
An undirected pattern is most useful when the underlying data is conceptually symmetric.
Multiple hops
Friends of friends — people who know each other indirectly — require expanding the query with a second hop from the second vertex to a third.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT * FROM GRAPH_TABLE (social MATCH (a IS person)-[IS knows]-> (b IS person)-[IS knows]->(c IS person) COLUMNS (a.name AS a, b.name AS via, c.name AS c) ) ORDER BY a, c, via; a | via | c -------+-------+------- Alice | Bob | Alice Alice | Carol | Bob Alice | Bob | Carol Alice | Carol | Dan Bob | Alice | Bob Bob | Carol | Bob Bob | Alice | Carol Bob | Carol | Dan Carol | Bob | Alice Carol | Bob | Carol Carol | Dan | Eve Dan | Eve | Frank Eve | Frank | Alice Frank | Alice | Bob Frank | Alice | Carol (15 rows) |
The data reveals something worth noticing: Alice knows Bob, and Bob knows Alice, so the traversal can return Alice → Bob → Alice. Semantically that is usually not wanted, and a WHERE clause filters it out.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT * FROM GRAPH_TABLE (social MATCH (a IS person)-[IS knows]-> (b IS person)-[IS knows]->(c IS person) WHERE a.id <> c.id COLUMNS (a.name AS a, b.name AS via, c.name AS c) ) ORDER BY a, c, via; a | via | c -------+-------+------- Alice | Carol | Bob Alice | Bob | Carol Alice | Carol | Dan Bob | Alice | Carol Bob | Carol | Dan Carol | Bob | Alice Carol | Dan | Eve Dan | Eve | Frank Eve | Frank | Alice Frank | Alice | Bob Frank | Alice | Carol (11 rows) |
What matters here is that the WHERE clause may sit inside the GRAPH_TABLE definition.
Under the hood
EXPLAIN shows what actually happens.
|
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 |
tutorial=# explain SELECT * FROM GRAPH_TABLE (social MATCH (a IS person)-[IS knows]-> (b IS person)-[IS knows]->(c IS person) WHERE a.id <> c.id COLUMNS (a.name AS a, b.name AS via, c.name AS c) ) ORDER BY a, c, via; QUERY PLAN ------------------------------------------------------------------ Sort (cost=575.72..588.55 rows=5132 width=96) Sort Key: person.name, person_2.name, person_1.name -> Hash Join (cost=145.96..259.45 rows=5132 width=96) Hash Cond: (knows_1.b = person_2.id) Join Filter: (person.id <> person_2.id) -> Hash Join (cost=117.74..217.66 rows=5138 width=72) Hash Cond: (knows.b = person_1.id) -> Hash Join (cost=28.23..64.01 rows=2040 width=40) 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=36) -> Seq Scan on person (cost=0.00..18.10 rows=810 width=36) -> Hash (cost=64.01..64.01 rows=2040 width=44) -> Hash Join (cost=28.23..64.01 rows=2040 width=44) Hash Cond: (knows_1.a = person_1.id) -> Seq Scan on knows knows_1 (cost=0.00..30.40 rows=2040 width=8) -> Hash (cost=18.10..18.10 rows=810 width=36) -> Seq Scan on person person_1 (cost=0.00..18.10 rows=810 width=36) -> Hash (cost=18.10..18.10 rows=810 width=36) -> Seq Scan on person person_2 (cost=0.00..18.10 rows=810 width=36) (20 rows) |
Crucially, no additional executor nodes appear in the plan. PostgreSQL rewrites the query behind the scenes, so the graph syntax is purely a more compact and readable way to express the joins between existing tables. CREATE PROPERTY GRAPH helps describe the data model in a form that enables that simplification.
What comes next
The next part of the series moves on to heterogeneous graphs and more complex data models in SQL/PGQ on PostgreSQL 19.
