Insert multiple rows with different values from a select

Question

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.

Michel Milezzi · Accepted Answer · 2017-11-08 22:52:52Z

6

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");

edited Nov 8, 2017 at 22:52

answered Nov 8, 2017 at 19:18

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rafael Berro Over a year ago

Is there a way to do only one select instead of three? thank you one more time @michel.milezzi.

Michel Milezzi Over a year ago

Yes, but IMHO not worth it. See my updated answer. Regards!

Marth Over a year ago

@michel.milezzi: Your "1-SELECT" solution is a bit over-complicated, you could do select ... from project, (values ('notes', 'textarea'), ('bio', 'text')) as t(name, "type"). But yes, using UNION ALL is, I believe, the most common solution (and the easiest/most readable too).

Marth Over a year ago

@RafaelBerro: Purely out of curiosity, why are you looking for a "single select" way to do this?

Michel Milezzi Over a year ago

@Marth you're right, I've updated my answer pointing it. Thanks

|

Collectives™ on Stack Overflow

Insert multiple rows with different values from a select

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related