INSERT rows in two tables preserving connection to a third table As part of a recent database development project, I'm stuck with a query for which I seem to find no elegant solution (i.e. 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?
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