CODEWITHSUNDEEP
postgresqlsql-insert
baudot
baudot

Reputation: 1618

SELECTING multiple copies of some rows, single copies of others for INSERT

In Postgres, I want to copy over rows from a table of templates into a table of instances. The trick is, I want to make multiple copies of some rows and only single copies of others.

One example of syntax that isn't working:

INSERT INTO owned_instances
(
  template_id,
  owner,
  property1,
  property2,
  etc
)
SELECT
  id,
  '478aa65e-ecf7-48fb-bc20-f7fcf300aaa9',
  property1,
  property2,
  etc
FROM templates
WHERE id IN (1, 1, 1, 2, 3);

The intent here would be that the owner would receive 3 copies of template 1, and 1 copy each of templates 2 and 3.

This version doesn't work. WHERE id IN only grabs one copy of each listed id. Other syntaxes I've tried have been equally fruitless.

Of course it's possible with multiple insert calls, but I have to wonder if there's a tidy & efficient way to do this in a single insert.

Attempting to anticipate one question: The copies in the table of instances will be individually mutated later. So the quick solve of just adding a column to count how many copies one row represents won't work in this case.

Upvotes: 2

Views: 185

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You are on the right track but you will need to join to a table containing the id values:

INSERT INTO owned_instances (template_id, owner, property1, property2, etc)
SELECT
    i.id,
    '478aa65e-ecf7-48fb-bc20-f7fcf300aaa9',
    t.property1,
    t.property2,
    t.etc
FROM templates t
INNER JOIN (
    SELECT 1 AS id UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 2 UNION ALL SELECT 3
) i
    ON i.id = t.id;

Upvotes: 2

Related Questions