CODEWITHSUNDEEP
postgresqlapache-age
Jesper
Jesper

Reputation: 3

Issue creating graph database using apache AGE extension

I'm trying to create a graph database from an existing relational database. So a little background information is that I have a tbale with product numbers and serial numbers and I want a graph database that illustrates their relations. I have a tree like structure in the product numbers, that's why I want to make a graph database with the apache AGE.

I have managed to run the command:

SELECT * FROM ag_catalog.create_graph('part_numbers_graph');

But when I run:

INSERT INTO part_numbers_graph
    SELECT agtype(format('{"id": %L, "label": "SSN", "properties": {"name": %L}}', SSN, SSN)::jsonb)
    FROM serial_table;

I get the following error:

relation "part_numbers_graph" does not exist

I have ran the command:

SELECT * FROM ag_catalog.ag_graph;

I can see that the graph is there.

 graphid |        name        |     namespace
---------+--------------------+--------------------
32924 | part_numbers_graph | part_numbers_graph

I have succesfully been able to run both

CREATE EXTENSION age;

and

LOAD 'age';

When I run

pg_config

I can see that I have installed VERSION = PostgreSQL 12.14 (Ubuntu 12.14-1.pgdg22.04+1)

Upvotes: 0

Views: 740

Answers (8)

Mahina Sheikh
Mahina Sheikh

Reputation: 15

You can use the cypher() function and write Cypher queries to define your graph structure.

SELECT * FROM cypher('part_numbers_graph', $$ 
CREATE (u:SSN {"id": 12345, "name": "John"})
RETURN u $$) AS (u agtype);

In this query, you are creating a node with the label "SSN" and two property fields: "id" and "name".

I hope this would help.

Upvotes: -1

Awais Bin Adil
Awais Bin Adil

Reputation: 17

The part_numbers_graph isn't a typical Postgres table. Instead, it's a graph table that holds data as vertices and edges. You need to use special commands called 'cypher queries' to put data into it.

    SELECT * FROM cypher('part_numbers_graph', $$ 
    CREATE (u:SSN {"id": "<insert_id>", "name": "<insert_name>""})
    RETURN u $$) AS (u agtype);

Upvotes: 0

MOIEZ IBRAR
MOIEZ IBRAR

Reputation: 23

1.CREATE EXTENSION age';

2.LOAD 'age';

  1. SELECT * FROM ag_catalog.ag_graph;

     CREATE TABLE part_numbers_graph (
  id bigserial primary key,
  label text,
  properties jsonb
);
  1. SELECT * FROM ag_catalog.ag_graph;
INSERT INTO part_numbers_graph
SELECT agtype(format('{"id": %L, "label": "SSN", "properties": {"name": %L}}', SSN, SSN)::jsonb)
FROM serial_table;

Upvotes: 0

Omar Saad
Omar Saad

Reputation: 408

You can't insert in the part_numbers_graph as inserting in a normal postgres table instead you should use the cypher function that is offered by the AGE extension.

So your query should be modified as follows:

SELECT * FROM cypher('part_numbers_graph', $$
MERGE (u:SSN {id: 'your_id' , name: 'your_name' }) 
$$) as (res agtype);

So this query will create a new vertex with label SSN and with two properties : id and name.

If the vertex already exists the vertex will be updated instead of creating new one because here I am using MERGE.

If you want to create a new vertex every time even if it is a duplicate you can use CREATE.

Upvotes: 0

Ken W.
Ken W.

Reputation: 389

You might want to first check if the relational table exists and that you are referencing it correctly as the error shows that it does not exist. The backslash command \dt can be used to check the existing list of relations you have.

Adding to the solutions already suggested here, you can also use prepared statements or pgsql functions to import data from a table into a graph, which you can read more about here

Upvotes: 0

Hammad Saleem
Hammad Saleem

Reputation: 237

Although you have successfully created the graph, the error stating "relation 'part_number_graph' does not exist suggest that it is not present in the current scchema. What I suggest is that you should create the above-mentioned table before insert query: Creating a table:

    CREATE TABLE part_numbers_graph (
  id bigserial primary key,
  label text,
  properties jsonb
);

Now, use the insert query:

INSERT INTO part_numbers_graph
SELECT agtype(format('{"id": %L, "label": "SSN", "properties": {"name": %L}}', SSN, SSN)::jsonb)
FROM serial_table;

Hope it helped!

Upvotes: 0

Panagiotis Foliadis
Panagiotis Foliadis

Reputation: 233

To insert values into a graph you should use the cypher function. For example in your case the query will look something like this :

SELECT * FROM cypher('part_numbers_graph', $$ 
CREATE (u:SSN {"id": "<insert_id>", "name": "<insert_name>""})
RETURN u $$) AS (u agtype);

With this query you create a node that has SSN label and has 2 property fields id and name. Age has its own way to create id's though so maybe you don't have to actually hard code your own id in the query and just let age keep track of the id's of the different nodes and edges.

Upvotes: 0

Safi50
Safi50

Reputation: 361

part_numbers_graph is not a regular Postgres table, but a graph table which stores data in the form of vertices and edges and requires data to be inserted using cypher queries. For more information above cypher queries, see the official Apache AGE documentation.

For your particular example, a cypher query would look something like:

SELECT * FROM cypher('part_numbers_graph', $$
CREATE (n:SSN {id: 'SSN' , name: 'SSN' }) 
$$) as (a agtype);

Upvotes: 0

Related Questions