Re: INSERT where not exists with foreign key

On 5/20/19 11:11 AM, Chuck Martin wrote:
> My Google foo isn't working on this question, probably because I don't 
> understand the question well enough. I'm using Postgres 11.3 on Centos 
> 7. I'm trying to insert a record in table A with a foreign key to table 
> B, but only where there is not already a foreign key in A to B. So 
> assume this simple structure:
> 
> Table A
> A.key    Integer
> A.something     text
> A.Bkey     Integer [foreign key to table B, column B.key
> 
> Table B (or View C)
> B.key    Integer
> [more columns]
> 
> Thinking that it might simplify matters, I created a view to table B 
> that only includes records with no foreign key in Table A. But still, 
> after reading the documentation and Googling, I can't work out what 
> seems like it should be simple. Among the many things that didn't work is:
> 
> INSERT INTO A(something,A.Bkey)
> 
> VALUES ('text',
>                (SELECT C.key FROM C)
> 
> But this didn't work because the subquery returned more than one value. 
> Of course I want it to return all values, but just one per insert.
> 
> I can do this outside of Postgres, but would like to learn how to do 
> this with SQL.

A more concrete example:

create table parent_tbl(id integer primary key, fld_1 varchar)

create table child_tbl(child_id serial primary key, fk_id integer 
references parent_tbl, child_fld_1 varchar);

insert into parent_tbl values (1, 'test');
insert into parent_tbl values (2, 'dog');

insert into child_tbl(fk_id, child_fld_1)  values (1, 'cat');
insert into child_tbl(fk_id, child_fld_1)  values (2, 'fish');

insert into child_tbl(fk_id, child_fld_1)  select id, 'rabbit' from 
parent_tbl where id not in(select child_id from child_tbl);

select * from child_tbl;
  child_id | fk_id | child_fld_1
----------+-------+-------------
         1 |     1 | cat
         2 |     2 | fish
         3 |     3 | rabbit

insert into parent_tbl values (4, 'parrot');
insert into parent_tbl values (5, 'lion');

insert into child_tbl(fk_id, child_fld_1)  select id, 'rabbit' from 
parent_tbl where id not in(select child_id from child_tbl);

select * from child_tbl;
  child_id | fk_id | child_fld_1
----------+-------+-------------
         1 |     1 | cat
         2 |     2 | fish
         3 |     3 | rabbit
         4 |     4 | rabbit
         5 |     5 | rabbit


> 
> Chuck Martin
> Avondale Software


-- 
Adrian Klaver
adrian.klaver@aklaver.com