INSERT where not exists with foreign key

From: Chuck Martin <clmartin(at)theombudsman(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: INSERT where not exists with foreign key
Date: 2019-05-20 18:11:50
Message-ID: CAFw6=U3Nh09ynmu+J950izZtpqRHP4znK1qYbR0meHyMBHyd8A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-05-20 18:12:56 Re: Loading table with indexed jsonb field is stalling
Previous Message Will Hartung 2019-05-20 18:05:35 Re: Loading table with indexed jsonb field is stalling