CODEWITHSUNDEEP
sqlpostgresql
Leevi L
Leevi L

Reputation: 1789

Best practice for creating two tables with references

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?

Upvotes: 0

Views: 47

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270723

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 ;

Upvotes: 1

Related Questions