Avoid repeating predefined value in SQL insert into

Question

I am looking to insert a couple of values associated to a pair of ids without hardcoding those ids in the query.
More concretely, I have this table foo:

create table if not exists foo(id int, val text);

and I can insert my values by doing:

insert into foo
values
  (10, 'qwe_1'),
  (10, 'qwe_2'),
  (10, 'qwe_3'),
  (20, 'qwe_2'),
  (20, 'asd_3'),
  (20, 'asd_4');

but I do not want to repeat those 10 and 20.

I asked a similar question a while ago (SQL - Using WITH to declare variable on INSERT INTO) but it does not solve my problem. Neither can I understand how to use a join or similar as suggested in INSERT repeating values in SQL, since the list of values I want to add per id is arbitrary.

While not strictly needed I would like to use a with statement to first declare my ids:

with c (first_id, second_id) as (values (10, 20))
select * from c;

but I don't understand how to combine that with the insert into statement. I have this non working query but which illustrates what I'm trying to achieve:

with c (first_id, second_id) as (values (10, 20))
insert into foo
values
  (c.first_id, 'qwe_1'),
  (c.first_id, 'qwe_2'),
  (c.first_id, 'qwe_3'),
  (c.second_id, 'qwe_2'),
  (c.second_id, 'asd_3'),
  (c.second_id, 'asd_4')
from c;

My understanding is that the values (...), ... statement returns a table so maybe what I am lacking is a way of combining this table with the c table.

score 3 · Accepted Answer · answered Aug 11 '20 at 17:48

You could use a lateral join:

insert into foo (id, val)
    select v.id, v.val
    from (values (10, 20)) c(first_id, second_id) cross join lateral
         (values (c.first_id, 'qwe_1'),
                 (c.first_id, 'qwe_2'),
                 (c.first_id, 'qwe_3'),
                 (c.second_id, 'qwe_2'),
                 (c.second_id, 'asd_3'),
                 (c.second_id, 'asd_4')
         ) v(id, val);

score 3 · Answer 2 · answered Aug 11 '20 at 17:57

3

If you're able to use a block structure, I would use that route.

do $$
DEFINE
    v_first_id  NUMBER := 10;
    v_second_id NUMBER := 20;
BEGIN
    ... Your Insert Statement ...
END; $$

answered Aug 11 '20 at 17:57

Ingenioushax

718
5
20

1

This looks a bit simpler, I managed to get it working with the following query: ``` do $$ DECLARE v_first_id integer := 10; v_second_id integer := 20; BEGIN insert into foo values (v_first_id, 'qwe_1'), (v_first_id, 'qwe_2'), (v_first_id, 'qwe_3'), (v_second_id, 'qwe_2'), (v_second_id, 'asd_3'), (v_second_id, 'asd_4'); END; $$ language plpgsql ``` – ffigari Aug 11 '20 at 18:15

Avoid repeating predefined value in SQL insert into

2 Answers2