Quettabyte

Defining Property Graphs in PostgreSQL 19: https://www.postgresql.org/docs/devel/ddl-property-graphs.html Relations Table definition: CREATE TABLE products ( product_no integer PRIMARY KEY, name varchar, price numeric ); CREATE TABLE customers ( customer_id integer PRIMARY KEY, name varchar, address varchar ); CREATE TABLE orders ( order_id integer PRIMARY KEY, ordered_when date ); CREATE TABLE order_items ( order_items_id integer PRIMARY KEY, order_id integer REFERENCES orders (order_id), product_no integer REFERENCES products (product_no), quantity integer ); CREATE TABLE customer_orders ( customer_orders_id integer PRIMARY KEY, customer_id integer REFERENCES customers (customer_id), order_id integer REFERENCES orders (order_id) ); Property Graph definition: CREATE PROPERTY GRAPH myshop VERTEX TABLES ( products, customers, orders ) EDGE TABLES ( order_items SOURCE orders DESTINATION products, customer_orders SOURCE customers DESTINATION orders ); get a list of customers who placed an order today using the ASCII Art based Pattern Matching ((node)->[edge]->(node)) SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders]->(o IS orders WHERE o.ordered_when = current_date) COLUMNS (c.name AS customer_name));

A friend-of-friend example: -- Define a graph over existing tables CREATE PROPERTY GRAPH social_network VERTEX TABLES (users LABEL person PROPERTIES (id, name)) EDGE TABLES ( related_to SOURCE KEY (person_a) REFERENCES users (id) DESTINATION KEY (person_b) REFERENCES users (id) LABEL friend ); -- Query: find friends of friends SELECT * FROM GRAPH_TABLE (social_network MATCH (a IS person WHERE a.name = 'Eleni') -[IS friend]->(b IS person) -[IS friend]->(c IS person) COLUMNS (b.name AS friend, c.name AS friend_of_friend) );

Here is an example of how to use SQL/PGQ to analyze email communication data. While this is a simple example, it shows the power of Graph Queries: CREATE TABLE users ( id integer NOT NULL, person_name varchar ); CREATE TABLE email ( id integer NOT NULL, sender integer, recipient integer, subject text ); -- user - sent -> user CREATE PROPERTY GRAPH communication_graph VERTEX TABLES ( users AS email_user KEY (id) PROPERTIES (id, person_name) ) EDGE TABLES ( email AS sent KEY (id) SOURCE KEY (sender) REFERENCES email_user (id) DESTINATION KEY (recipient) REFERENCES email_user (id) ) ; INSERT INTO users VALUES (1, 'Uroosa'); INSERT INTO users VALUES (2, 'Fatima'); INSERT INTO users VALUES (3, 'Hannah'); INSERT INTO email VALUES (1, 1, 2, 'vacation plan'); -- From Uroosa to Fatima INSERT INTO email VALUES (2, 2, 1, 'vacation plan'); -- From Fatima to Uroosa INSERT INTO email VALUES (3, 1, 3, 'transportation inquiry'); -- From Uroosa to Hannah -- Who did Uroosa received emails from SELECT * FROM GRAPH_TABLE(communication_graph MATCH (a IS email_user WHERE a.person_name = 'Uroosa') <-[IS sent]- (b IS email_user) COLUMNS (b.person_name) ); -- output Fatima (1 row) -- Who did Uroosa emailed? SELECT * FROM GRAPH_TABLE(communication_graph MATCH (a IS email_user WHERE a.person_name = 'Uroosa') -[IS sent]-> (b IS email_user) COLUMNS (b.person_name) ); -- output Fatima Hannah (2 rows)