Postgres: INSERT the results of a SELECT query that has a WITH statement

Question

In a PostgreSQL 9.5 database I have objects of different types (each stored in its own table) which are interrelated in a DAG. To represent this DAG I have set up node and edge tables. The node table simply stores a globally unique id, which will be shared by all the tables storing objects:

CREATE TABLE node (
  id SERIAL PRIMARY KEY NOT NULL
);

Let’s say one of my object types is in table library. Ignoring other fields, this table is simply:

CREATE TABLE library (
  uuid INTEGER NOT NULL REFERENCES node(id),
  id TEXT PRIMARY KEY NOT NULL
)

I can load a single entry with id XXX into library as follows:

WITH x AS (
  INSERT INTO node (id) VALUES (DEFAULT) RETURNING id
)
INSERT INTO library (id, uuid) VALUES ('XXX', (SELECT id FROM x));

However, I have a view import.library that references an external resource, and I want to load public.library en masse from that view. In other words, I want to do something like this:

INSERT INTO library (uuid, id)
  WITH x AS (
    INSERT INTO node (id) VALUES (DEFAULT) RETURNING id
  )
  SELECT (SELECT id FROM x), id FROM import.library;

That is not possible, since a data-modifying WITH statement has to be at the top level.

I can achieve the same result by using an anonymous function in a DO statement:

DO $$DECLARE l_id TEXT;
BEGIN
  FOR x IN SELECT id FROM import.library
  LOOP
    WITH x AS (
      INSERT INTO node (id) VALUES (DEFAULT) RETURNING id
    )
    INSERT INTO library (id, uuid) VALUES (l_id, (SELECT id FROM x));
  END LOOP;
END$$;

This works, but I would still like to know whether it’s possible to do the same in a simple SQL statement without resorting to an anonymous function.

score 2 · Accepted Answer · 2016-04-17 20:13:45Z

2

As the uuid in the node table is generated by a sequence, the only way I can think of is to flip the order of insertion:

WITH x AS (
  INSERT INTO library (uuid, id)
  SELECT nextval('node_id_seq'), id 
  FROM import_library
  returning uuid  
)
INSERT INTO node (id) 
select uuid
from x

edited Apr 17, 2016 at 20:13

answered Apr 17, 2016 at 18:59

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

wjv Over a year ago

That’s really thinking outside the box! Yup, that seems to do the trick, so I’m going to hit ‘Accept’ on that… Thanks!

Collectives™ on Stack Overflow

Postgres: INSERT the results of a SELECT query that has a WITH statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related