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.

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(dot)klaver(at)aklaver(dot)com