0

I am computing a graph with nodes n1, n2, ... and edges (n_i, n_j). I would like to store it in two postgresql tables. The first is called nodes and has a node id column that is defined by a node_id SERIAL column. The seconds is called edges and has a column start_node_id and end_node_id that are references into the nodes table and the node id column.

When I insert the nodes I don't know their node ids. So to insert the corrsponding edges I need to query the node ids that were created when I inserted the nodes. Is there a "neater way" of achieving this? What's is you best practice in a situation like this?

Leevi L
  • 1,538
  • 2
  • 13
  • 28

2 Answers2

1

If you want to refer to nodes by name, you can insert into edges by looking up the ids:

insert into edges (start_node_id, end_node_id)
    select ns.node_id, ne.node_id
    from (values ('n1', 'n2')) e(start_node_name, end_node_name) join
         nodes ns
         on ns.node_name = e.start_node_name join
         nodes ne
         on ne.node_name = e.end_node_name ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can use RETURNING to get access to the data you just inserted.

In your other question, you report you're using Python; this question shows how to access the RETURNING values.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • Interesting. I couldn't get it to work in my context: https://stackoverflow.com/questions/59085374/psycopg2-returns-none-when-execute-many-is-combined-with-returning-id – Leevi L Nov 28 '19 at 09:21
  • https://stackoverflow.com/questions/5247685/python-postgres-psycopg2-getting-id-of-row-just-inserted – Neville Kuyt Nov 28 '19 at 11:03