Insert multiple rows with different values from a select

user_5905789 0
Viewed 1782

I have the following tables Projects and ProjectFields. I am trying to populate one with values from another plus some other values, this way:

INSERT INTO "ProjectFields" ("projectId", name, type, "isDefault")
      (SELECT "projectId", 'Notes', 'textarea', true FROM "Projects"), -- creates one field called 'Notes' for every project.
      (SELECT "projectId", 'Bio', 'textarea', true FROM "Projects"), -- creates one field called 'Bio' for every project.
      (SELECT "projectId", 'Photo', 'text', true FROM "Projects"); -- creates one field called 'Photo' for every project.

The idea is that every project must have three referenced fields in the ProjectFields table. I know that the code above doesn't work, is there any way to do this using PostgreSQL ?

If you have a better idea please tell me, thank you.

postgresql
1 Answers

You're almost there! Just use UNION and voilá:

INSERT INTO "ProjectFields" ("projectId", name, type, "isDefault")
      SELECT "projectId", 'Notes', 'textarea', true FROM "Projects"
        UNION ALL
      SELECT "projectId", 'Bio', 'textarea', true FROM "Projects"
        UNION ALL
      SELECT "projectId", 'Photo', 'text', true FROM "Projects";

--As you asked, using only one SELECT on Projects (but using a CTE)
--But I didn't recommend this solution, why increase complexity of a query just to avoid the 3 SELECTS?
INSERT INTO "ProjectFields" ("projectId", name, "isDefault")
    WITH tmp (proj_id, field_name, is_default) AS (     
        SELECT "projectId", UNNEST(ARRAY['Notes','Bio', 'Photo']), true FROM "Projects"
    )
    SELECT proj_id, field_name, CASE field_name WHEN 'Notes' THEN 'textarea' WHEN 'Bio' THEN 'textarea' ELSE 'text' END,is_default FROM tmp;

--Or perhaps in a more cleaner way as stated by @Marth:
INSERT INTO "ProjectFields" ("projectId", name, "isDefault")
    SELECT * FROM projects, (VALUES ('notes', 'textarea'), ('bio', 'textarea'), ('Photo', 'text')) AS t(name, "type");
user_79253660
Related