CODEWITHSUNDEEP
sqlpostgresqlsql-insert
TwoThe
TwoThe

Reputation: 14309

PostgreSQL: How to insert multiple values without multiple selects?

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?

Upvotes: 2

Views: 590

Answers (3)

Abelisto
Abelisto

Reputation: 15624

Yet another solution:

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

Upvotes: 4

user5883842
user5883842

Reputation:

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;

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 4

Related Questions