PostgreSQL: How to insert multiple values without multiple selects?

Question

I have the problem that I need to insert into a table with 2 entries, where one value is constant but fetched from another table, and the other one is the actual content that changes.

Currently I have something like

INSERT INTO table (id, content) VALUES 
  ((SELECT id FROM customers WHERE name = 'Smith'), 1),
  ((SELECT id FROM customers WHERE name = 'Smith'), 2),
  ((SELECT id FROM customers WHERE name = 'Smith'), 5),
  ...

As this is super ugly, how can I do the above in Postgres without the constant SELECT repetition?

Abelisto · Accepted Answer · 2016-02-16 16:27:59Z

4

Yet another solution:

insert into table (id, content)
select id, unnest(array[1, 2, 5]) from customers where name = 'Smith';

answered Feb 16, 2016 at 16:27

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

1 Comment

TwoThe Over a year ago

The answer from a_horse_with_no_name is also valuable but this one is closer to the actual question. I wish I could accept two answers. ;)

user330315 · Accepted Answer · 2016-02-16 16:08:36Z

4

You can cross join the result of the select with your values:

INSERT INTO table (id, content) 
select c.id, d.nr 
from (
  select id 
  from customers 
  where name = 'Smith'
) as c 
  cross join (values (1), (2), (5) ) as d (nr);

This assumes that the name is unique (but so does your original solution).

answered Feb 16, 2016 at 16:08

user330315

2 Comments

Abelisto Over a year ago

Just for fun: select id, unnest(array[1, 2, 5]) from customers where name = 'Smith'

TwoThe Over a year ago

You should make that into an answer Abelisto.

user5883842 · Accepted Answer · 2016-02-16 16:14:03Z

0

Well, I believe you can do something like this:

DECLARE
id customers.id%TYPE; 
BEGIN
select c.id into id FROM customers c WHERE name = 'Smith';
INSERT INTO table (id, content) VALUES 
(id, 1),
(id, 2),
....
END;

answered Feb 16, 2016 at 16:14

user5883842

Collectives™ on Stack Overflow

PostgreSQL: How to insert multiple values without multiple selects?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related