SQL/PGQ — Graph Pattern Matching

Create property graphs over existing tables and query them with Cypher-like MATCH patterns, composable with full SQL analytics.

CREATE PROPERTY GRAPH

Define a property graph by declaring vertex tables and edge tables over existing relational tables. Teide automatically builds compressed sparse row (CSR) indexes on creation for efficient traversal.

Syntax:

CREATE PROPERTY GRAPH graph_name
  VERTEX TABLES (
    table_name
      KEY (column_name)
      LABEL label_name
      PROPERTIES (col1, col2, ...)
  )
  EDGE TABLES (
    table_name
      KEY (column_name)
      SOURCE KEY (src_col) REFERENCES vertex_table (key_col)
      DESTINATION KEY (dst_col) REFERENCES vertex_table (key_col)
      LABEL label_name
      PROPERTIES (col1, col2, ...)
  );

Example — a social network with persons and friendships:

-- Relational tables
CREATE TABLE persons (id INTEGER, name VARCHAR, age INTEGER);
INSERT INTO persons VALUES (1, 'Alice', 30), (2, 'Bob', 25),
                           (3, 'Carol', 35), (4, 'Dave', 28);

CREATE TABLE friendships (id INTEGER, src INTEGER, dst INTEGER, since DATE);
INSERT INTO friendships VALUES (1, 1, 2, '2020-01-15'),
                               (2, 1, 3, '2019-06-01'),
                               (3, 2, 4, '2021-03-22'),
                               (4, 3, 4, '2022-11-10');

-- Property graph (builds CSR indexes automatically)
CREATE PROPERTY GRAPH social
  VERTEX TABLES (
    persons KEY (id) LABEL Person PROPERTIES (name, age)
  )
  EDGE TABLES (
    friendships KEY (id)
      SOURCE KEY (src) REFERENCES persons (id)
      DESTINATION KEY (dst) REFERENCES persons (id)
      LABEL Knows
      PROPERTIES (since)
  );

DROP PROPERTY GRAPH

DROP PROPERTY GRAPH social;
DROP PROPERTY GRAPH IF EXISTS social;

GRAPH_TABLE with MATCH

Use GRAPH_TABLE in a FROM clause to run pattern matching queries against a property graph. The MATCH clause specifies the graph pattern, and the COLUMNS clause projects properties out as relational columns.

Basic 1-Hop Pattern

SELECT *
FROM GRAPH_TABLE (social
  MATCH (a:Person)-[:Knows]->(b:Person)
  COLUMNS (a.name AS person, b.name AS friend)
);
-- Alice | Bob
-- Alice | Carol
-- Bob   | Dave
-- Carol | Dave

With WHERE Filter

Predicates can be placed directly inside vertex or edge patterns:

SELECT *
FROM GRAPH_TABLE (social
  MATCH (a:Person WHERE a.name = 'Alice')-[:Knows]->(b:Person)
  COLUMNS (a.name AS person, b.name AS friend, b.age AS friend_age)
);
-- Alice | Bob   | 25
-- Alice | Carol | 35

COLUMNS Clause

The COLUMNS clause maps graph element properties to output columns. Each entry follows the form element.property AS alias:

SELECT *
FROM GRAPH_TABLE (social
  MATCH (a:Person)-[e:Knows]->(b:Person)
  COLUMNS (a.name AS src, b.name AS dst, e.since AS friends_since)
);

Variable-Length Paths

Quantifiers on edge patterns enable multi-hop traversal.

Bounded Range {min,max}

-- Friends within 1 to 3 hops
SELECT *
FROM GRAPH_TABLE (social
  MATCH (a:Person WHERE a.name = 'Alice')
        -[:Knows]->{1,3}
        (b:Person)
  COLUMNS (a.name AS src, b.name AS reachable)
);
-- Alice | Bob
-- Alice | Carol
-- Alice | Dave

One or More +

-- Transitive closure (1+ hops)
SELECT *
FROM GRAPH_TABLE (social
  MATCH (a:Person)-[:Knows]->+(b:Person)
  COLUMNS (a.name AS src, b.name AS reachable)
);

Zero or More *

-- Reflexive transitive closure (0+ hops, includes self)
SELECT *
FROM GRAPH_TABLE (social
  MATCH (a:Person)-[:Knows]->*(b:Person)
  COLUMNS (a.name AS src, b.name AS reachable)
);

Shortest Path

Use ANY SHORTEST to find a single shortest path between nodes. Bind the path to a variable to use path_length().

SELECT *
FROM GRAPH_TABLE (social
  MATCH ANY SHORTEST
    (a:Person WHERE a.name = 'Alice')
    -[p:Knows]->+
    (b:Person WHERE b.name = 'Dave')
  COLUMNS (a.name AS src, b.name AS dst, path_length(p) AS hops)
);
-- Alice | Dave | 2

Edge Directions

Patterns support three edge directions:

-- Forward (outgoing edges)
MATCH (a:Person)-[:Knows]->(b:Person)

-- Reverse (incoming edges)
MATCH (a:Person)<-[:Knows]-(b:Person)

-- Undirected (either direction)
MATCH (a:Person)-[:Knows]-(b:Person)

Composable with SQL

GRAPH_TABLE returns a standard relation, so it composes freely with all SQL constructs: GROUP BY, ORDER BY, JOIN, window functions, CTEs, and subqueries.

Top 10 Most Connected People

SELECT person, COUNT(*) AS connections
FROM GRAPH_TABLE (social
  MATCH (a:Person)-[:Knows]-(b:Person)
  COLUMNS (a.name AS person)
)
GROUP BY person
ORDER BY connections DESC
LIMIT 10;

Graph Results as CTE, Joined with Other Tables

WITH reachable AS (
  SELECT *
  FROM GRAPH_TABLE (social
    MATCH (a:Person WHERE a.name = 'Alice')
          -[:Knows]->{1,3}
          (b:Person)
    COLUMNS (b.name AS friend_name, b.age AS friend_age)
  )
)
SELECT r.friend_name, r.friend_age, o.city
FROM reachable r
INNER JOIN locations o ON r.friend_name = o.name
ORDER BY r.friend_age;