Recommened way to create from a table generally

Question

What is the recommended way to create a graph from a table (or from an SQL query, in general)?

Say, we have a table A with fields id and parent_id, where the latter refers back to the former of some row. How would I go about and cast this into an AGE graph easily? Ideally, this would be without doing a client-server roundtrip of first querying table rows and then creating the corresponding graph vertices and edges.

Currently, there seems to be a way to load graphs from CSV files (https://age.apache.org/age-manual/master/intro/agload.html), but this doesn't really help in this case. Also, there seems to be a helper function written in plpython available (https://github.com/sorrell/age-compose/blob/master/docker-entrypoint/initdb.d/20-initgraph.sql#L10-L37), but this isn't ideal either.

Answer 1

I think you have to do manually. I don’t believe that it’s too much work, the graph doesn’t hold columns, the nodes through labels and properties do. So you create the graph and then create the CSV file and load them through the methods you posted.

Answer 2

If you prefer a manual approach without relying on CSV files or utilities, you can create the nodes and edges directly within the graph database. I think it's the best way to solve it.

Answer 3

Refer to this answer posted to a similar question where the user have suggested to make a PostgreSQL function that takes in the arguments (rows as nodes, tables as labels, and joins as relationships) to create a node for every row in the table.

Answer 4

There are a few ways to achieve this. One way is to create CSV and then load the graph from CSV. If you do not want to rely on CSVs, you can write a Postgresql function to create nodes in the graph. You can refer to this question to see how both ways work.

Answer 5

Here is the code that you can change with your requirements to get the desired output.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, ARRAY[]::integer[] AS path
  FROM your_table
  WHERE parent_id IS NULL
  UNION ALL
  SELECT child.id, child.parent_id, parent.path || parent.id
  FROM your_table child
  JOIN tree parent ON parent.id = child.parent_id
)
INSERT INTO your_graph_nodes (node_id)
SELECT DISTINCT id FROM tree;

INSERT INTO your_graph_edges (source_node_id, target_node_id)
SELECT DISTINCT t1.id, t2.id
FROM tree t1
JOIN tree t2 ON t1.path @> t2.path;

Answer 6

It can be done. To create a graph from a table, you'll have to create a PostgreSQL function to handle that but take note that rows are nodes and foreign keys are the edges between the nodes.

Answer 7

You can take a number of strategies to complete this challenge. One approach entails creating a CSV file, which is then used to import the graph data. Alternatively, if you'd rather not use CSV files, you can develop a unique PostgreSQL function that will directly populate the graph's nodes. With this method, you may manage your graph data more precisely and individually within the PostgreSQL database system.