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.

Some examples that you can modify:
https://www.postgresql.org/docs/11/sql-insert.html

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

WITH upd AS (
UPDATE employees SET sales_count = sales_count + 1 WHERE id =
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

Chuck Martin
Avondale Software

--
Adrian Klaver
adrian.klaver@aklaver.com

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