Thread: INSERT where not exists with foreign key
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.
Chuck Martin
Avondale Software
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