Quick Start

Create the extension

Use a privileged account to run the following statement. To create a privileged account, see Create a database account .

CREATE EXTENSION age;

Configure the database

For each connection, add ag_catalog to the search_path to simplify queries, and load the extension using the get_cypher_keywords function:

SET search_path = ag_catalog, "$user", public;
SELECT * FROM get_cypher_keywords() limit 0;

We strongly recommend that a privileged account configure database parameters to load the extension permanently. This avoids repeating the above steps on every connection and simplifies usage.

ALTER DATABASE <dbname> SET search_path = "$user", public, ag_catalog;
ALTER DATABASE <dbname> SET session_preload_libraries TO 'age';

Grant AGE access to regular users

Grant USAGE permission on the ag_catalog schema to regular users.

GRANT USAGE ON SCHEMA ag_catalog TO <username>;

If the regular user is an RW user, also grant the CREATE permission to allow table creation.

GRANT CREATE ON DATABASE <dbname> TO <username>;

Query structure

Cypher queries in PolarDB are built by calling the cypher function in ag_catalog, which returns a SETOF records. Here is a typical query:

SELECT * FROM cypher('graph_name', $$
/* Write your Cypher query here */
$$) AS (result1 agtype, result2 agtype);

Here, graph_name is the name of the graph, and replace the /*  */ block with your actual Cypher query.

Create a graph

Before using a graph, create it first. Use the create_graph function in the ag_catalog namespace.

Syntax:

SELECT create_graph('<graph_name>');

Example:

Create a graph named moviedb.

SELECT create_graph('moviedb');

Insert data

Run the following SQL statement to insert sample data into the moviedb graph:

SELECT * FROM cypher('moviedb', $$
  CREATE (matrix:Movie {title: 'The Matrix', released: 1997})
  CREATE (cloudAtlas:Movie {title: 'Cloud Atlas', released: 2012})
  CREATE (forrestGump:Movie {title: 'Forrest Gump', released: 1994})
  CREATE (keanu:Person {name: 'Keanu Reeves', born: 1964})
  CREATE (robert:Person {name: 'Robert Zemeckis', born: 1951})
  CREATE (tom:Person {name: 'Tom Hanks', born: 1956})
  CREATE (tom)-[:ACTED_IN {roles: ['Forrest']}]->(forrestGump)
  CREATE (tom)-[:ACTED_IN {roles: ['Zachry']}]->(cloudAtlas)
  CREATE (robert)-[:DIRECTED]->(forrestGump)
$$) AS (result1 agtype);

This creates six nodes—three labeled Movie and three labeled Person—and three edges: two labeled ACTED_IN and one labeled DIRECTED. The relationship diagram is shown below:

Query data

SELECT * FROM cypher('graph_name', $$
  MATCH <patterns>
  RETURN <variables>
$$) AS (result1 agtype);

Create nodes or edges

In Cypher, use the CREATE keyword to add new nodes or edges.

Syntax:

SELECT * FROM cypher('<graph_name>', $$
  CREATE <patterns>
$$) AS (result1 agtype);

Examples:

• Create a Person node with name Tom Tykwer and birth year 1965.

  SELECT * FROM cypher('moviedb', $$
    CREATE (:Person {name: 'Tom Tykwer', born: 1965})
  $$) AS (result1 agtype);

• Create a Tom Tykwer–Cloud Atlas edge indicating that Tom Tykwer directed Cloud Atlas.

  SELECT * FROM cypher('moviedb', $$
    MATCH (p:Person), (m:Movie)
    WHERE p.name='Tom Tykwer' AND m.title='Cloud Atlas'                 
    CREATE (p)-[:DIRECTED]->(m)
  $$) AS (result1 agtype);

Use MERGE to avoid duplicate inserts

Running the same CREATE statement multiple times inserts duplicate data. To prevent this, use the MERGE keyword. MERGE checks whether data already exists. If it does, it returns the existing data or updates the node or relationship. If not, it creates new data based on the specified pattern.

Syntax:

SELECT * FROM cypher('<graph_name>', $$
  MERGE <patterns>
$$) AS (result1 agtype);

Examples:

• Create a Person node with name Tom Cruise and birth year 1962.

  SELECT * FROM cypher('moviedb', $$
    MERGE (:Person {name: 'Tom Cruise', born: 1962})
  $$) AS (result1 agtype);

• Create a friendship edge between Tom Hanks and Tom Cruise.

  SELECT * FROM cypher('moviedb', $$
    MATCH (t1:Person),(t2:Person)
    WHERE t1.name='Tom Hanks' AND t2.name='Tom Cruise'                 
    MERGE (t1)-[:FRIEND]-(t2)
  $$) AS (result1 agtype);

Update

To modify properties of existing nodes or relationships, match the desired pattern and use the SET keyword to add or update properties.

Syntax:

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  SET <property>
  RETURN <variable>
$$) AS (result1 agtype);

Examples:

• Change the birth year of Tom Tykwer to 1970.

  SELECT * FROM cypher('moviedb', $$
    MATCH (p:Person {name: 'Tom Tykwer', born: 1965})
    SET p.born = 1970
    RETURN p
  $$) AS (result1 agtype);

• Add a gender property with value male to Tom Tykwer.

  SELECT * FROM cypher('moviedb', $$
    MATCH (p:Person {name: 'Tom Tykwer', born: 1970})
    SET p.gender = 'male'
    RETURN p
  $$) AS (result1 agtype);

Delete

In Cypher, use the DELETE keyword to remove nodes and edges.

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  DELETE <variable>
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person)-[r:DIRECTED]->(m:Movie)
  WHERE p.name='Tom Tykwer' AND m.title='Cloud Atlas'                 
  DELETE r
$$) AS (result1 agtype);

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  DELETE <variable>
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person {name: 'Tom Tykwer', born: 1970})              
  DELETE p
$$) AS (result1 agtype);

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  DETACH DELETE <variable>
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person {name: 'Tom Hanks', born: 1956})              
  DETACH DELETE p
$$) AS (result1 agtype);

SELECT * FROM cypher('<graph_name>', $$
  MATCH <match_pattern>
  REMOVE <property>
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (m:Movie {title: 'Cloud Atlas', released: 2012})              
  REMOVE m.released
  RETURN m
$$) AS (result1 agtype);

Visualization tool

PolarDB for PostgreSQL integrates the open-source graph engine Apache AGE, letting you use both standard SQL and the industry-standard OpenCypher graph query language in the same cluster. This enables efficient storage, querying, and analysis of graph data for complex relationship scenarios. For more information, see Graph applications.

In AGE Viewer, run the Cypher query SELECT * from cypher('moviedb', $$ MATCH (V) RETURN V UNION MATCH ()-[r]->() RETURN r $$) as (V agtype); on the moviedb graph. The visualization shows three Movie nodes and three Person nodes (Tom Hanks, Keanu Reeves, Robert Zemeckis), along with two ACTED_IN edges and one DIRECTED edge, laid out using the Cose-Bilkent layout.