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.

Gordon Linoff · Accepted Answer · 2020-08-11 17:48:17Z

3

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

answered Aug 11, 2020 at 17:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Ingenioushax · Accepted Answer · 2020-08-11 17:57:15Z

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, 2020 at 17:57

Ingenioushax

7185 silver badges20 bronze badges

1 Comment

ffigari Over a year ago

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 ```

Collectives™ on Stack Overflow

Avoid repeating predefined value in SQL insert into

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related