PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Use value found by SELECT for multi-value INSERT

Noob question, but I cannot find answer anywhere. I want to pre-populate my database with some values, which refer to each other via foreign keys. But I see no way to do that without explicitly using IDs, which are supposed to be auto generated and therefore invisible to me. Approach which I hoped would work:

create table A
(
    id  INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    str TEXT NOT NULL
);

insert into A(str)
values ('a'),
       ('b'),
       ('c');

create table B
(
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    a  INT REFERENCES A
);

with found as (select id from A where str = 'b')
insert
into B(a)
values (found),
       (found),
       (found);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It seems that only some expressions are allowed as VALUES. I cannot use the table name nor (contrary to the documentation) column reference found.id. But I can use a scalar subquery (select * from found) or in simpler form (table found):

    create table A
(
    id  INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    str TEXT NOT NULL
);

insert into A(str)
values ('a'),
       ('b'),
       ('c');

create table B
(
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    a  INT REFERENCES A
);

with found as (select id from A where str = 'b')
insert
into B(a)
values ((TABLE found)),
       ((TABLE found)),
       ((TABLE found));

    which is ugly, but works.


  2. 0

    Add a returning clause to your insert into A, wrap that in a with common table expression or a subquery, then select from that when you insert into B. Demo at db<>fiddle:

    with inserted_into_a as (
    insert into A(str)
    values ('a'),
           ('b'),
           ('c')
    returning id,str)
insert into B(a) select id from inserted_into_a;

    select * from A;
    id str
    1 a
    2 b
    3 c 
    select * from B;
    id a
    1 1
    2 2
    3 3

    Remember that in PostgreSQL, unless you double-quote your identifiers, they are folded to lowercase, so A is just a, unless you create it as "A".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search