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
