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) );
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.
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?