From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Chuck Martin <clmartin(at)theombudsman(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: INSERT where not exists with foreign key |
Date: | 2019-05-20 18:30:34 |
Message-ID: | 7fb55314-d630-1306-9b00-cd5513062f4d@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | PegoraroF10 | 2019-05-20 20:18:00 | Re: Refresh Publication takes hours and doesn´t finish |
Previous Message | Adrian Klaver | 2019-05-20 18:18:02 | Re: INSERT where not exists with foreign key |