INSERT rows in two tables preserving connection to a third table

Question

I'm stuck with a query for which I can't seem to find and elegant solution without looping.

Relevant bits of the schema:

CREATE TABLE sites (
  site_id SERIAL PRIMARY KEY,
  ...
);

CREATE TABLE properties (
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL,
  FOREIGN KEY (property_def_id) REFERENCES property_def(property_def_id)
);

CREATE TABLE site_properties (
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

The task:
Insert one new, distinct property in table properties for each row in table sites and also insert a row in site_properties to store the relation between both.

Desired behaviour:
For each site_id (as my_site_id) in sites:

INSERT INTO properties (property_def_id)
VALUES (1)  
RETURNING property_id INTO my_prop_id;

INSERT INTO site_properties (property_id, site_id)
VALUES (my_prop_id, my_site_id);

At the end, I would have in the site_properties table each "new" property having being assigned only to one site (i.e. no cross-joins)

I feel it should be possible to do this in one query, but I couldn't figure out how for the love of me. I have tried CTEs but I ended up being stuck with a list of property_id's, a list of sites and no way to join between the two.

What am I missing?

Answer 1

Problem

Expressions in the RETURNING clause of an INSERT can only involve columns from inserted rows, not columns from additional tables joined in. That makes it hard to associate each newly inserted row in the table properties to one particular sites.site_id.

The manual on INSERT:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed.

Bold emphasis mine.

Solution

Use nextval() from the family of sequence functions to solve this cheaply and elegantly. Used to be the canonical way before RETURNING was introduced for INSERT with Postgres 8.2, and still works.

Use pg_get_serial_sequence() to get the name of the underlying sequence for the serial column:

WITH site_prop AS (
   SELECT site_id
        , nextval(pg_get_serial_sequence('properties', 'property_id')) AS prop_id
   FROM   sites
   )
, props AS (
   INSERT INTO properties (property_id, property_def_id)
   SELECT prop_id, 1
   FROM   site_prop
   )
INSERT INTO site_properties (site_id, property_id)
SELECT site_id, prop_id
FROM   site_prop;

This way you only need a single scan of table sites and neither window functions nor joins. Much cheaper.

Better yet

It would seem like a FK violation to insert rows with new property_id into site_properties while the key does not exist in properties, yet. But that's not the case if you insert the keys into properties in the same command. CTEs are considered part of the same command. The manual:

A constraint that is not deferrable will be checked immediately after every command.

So we can simplify some more, using next_val() and the RETURNING clause:

WITH site_prop AS (
   INSERT INTO site_properties (site_id, property_id)
   SELECT site_id
        , nextval(pg_get_serial_sequence('properties', 'property_id'))
   FROM   sites
   RETURNING site_id, property_id
   )
INSERT INTO properties (property_id, property_def_id)
SELECT property_id, 1
FROM   site_prop;

Related:

• PostgreSQL next value of the sequences?
• Inserting into related tables

Answer 2

It took me an awfully long while to figure it out, but here it goes:

Setup:

CREATE TABLE sites
(
  site_id INT PRIMARY KEY
);

CREATE TABLE properties
(
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL
);

CREATE TABLE site_properties
(
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

INSERT INTO sites VALUES (101),(102),(103);

INSERT INTO properties(property_def_id) VALUES (3), (4);
INSERT INTO site_properties(property_id, site_id) VALUES (1, 101), (2, 101);

Actual query:

WITH props AS (
  INSERT INTO properties (property_def_id)
    SELECT 1 FROM sites
  RETURNING property_id
), new_props AS (
  SELECT row_number() OVER () AS row, property_id
    FROM props
), all_sites AS (
  SELECT row_number() OVER () AS row, site_id
    FROM sites
)
INSERT INTO site_properties (site_id, property_id)
SELECT s.site_id, np.property_id
FROM all_sites s
  JOIN new_props np USING (row)
;

Checking everything works as expected:

SELECT *
FROM site_properties
  JOIN properties USING (property_id);


property_id | site_id | property_def_id
---------------------------------------
     1          101            3
     2          101            4
     3          101            1
     4          102            1
     5          103            1