Kuzu's "Getting Started" ported to DuckPGQ

duckpgq-demo.sql

-- Adapted from https://docs.kuzudb.com/get-started/

-- Install and load SQL/PGQ extension
INSTALL duckpgq FROM community;
LOAD duckpgq;

-- City vertex table
CREATE TABLE City AS SELECT * FROM read_csv(
  'https://github.com/kuzudb/kuzu/raw/master/dataset/demo-db/csv/city.csv',
  names=[name, population]
);

-- User vertex table
CREATE TABLE User AS SELECT * FROM read_csv(
  'https://github.com/kuzudb/kuzu/raw/master/dataset/demo-db/csv/user.csv',
  names=[name, age]
);

-- LivesIn edge table
CREATE TABLE LivesIn AS SELECT * FROM read_csv(
  'https://github.com/kuzudb/kuzu/raw/master/dataset/demo-db/csv/lives-in.csv',
  names=['user', city]
);

-- Follows edge table
CREATE TABLE Follows AS SELECT * FROM read_csv(
  'https://github.com/kuzudb/kuzu/raw/master/dataset/demo-db/csv/follows.csv',
  names=[follower, followee, since]
);

-- Create property graph
CREATE PROPERTY GRAPH demo
  VERTEX TABLES (
    City,
    User
  )
  EDGE TABLES (
    LivesIn SOURCE KEY (user) REFERENCES User (name)
            DESTINATION KEY (city) REFERENCES City (name),
    Follows SOURCE KEY (follower) REFERENCES User (name)
            DESTINATION KEY (followee) REFERENCES User (name)
  );

-- Sample Queries

FROM GRAPH_TABLE (demo MATCH (a:User)-[f:Follows]->(b:User)
  COLUMNS (a.name, b.name, f.since));

FROM GRAPH_TABLE (demo MATCH (a:User)-[r:LivesIn]->(c:City)
  COLUMNS (a.name, c.name, c.population));

FROM GRAPH_TABLE (demo
  MATCH (a:User)-[f:Follows]->(b:User)-[l:LivesIn]->(c:City)
  WHERE c.name = 'Kitchener'
  COLUMNS (a.name, b.name)
);

FROM GRAPH_TABLE (demo
  MATCH (a:User)-[m:Follows]->(h:User)<-[n:Follows]-(b:User)
  WHERE a.name != b.name
  COLUMNS (a.name, b.name, h.name)
);

FROM GRAPH_TABLE (demo
  MATCH p = ANY SHORTEST (a:User WHERE a.name = 'Adam')-[f:Follows]->+(b:User)
  COLUMNS (element_id(p), vertices(p), edges(p), path_length(p), b.name)
) ORDER BY name LIMIT 5;