Postgres - insert and composite foreign keys

Question

Say I have a table with the following columns:

a:    integer
b:    integer
c:    integer
d:    integer
code: text

(a, b) is a foreign key to another table 1
(c, d) is a foreign key to another table 2

Inserting is easy:

INSERT INTO table(a, b, c, d, code) VALUES(x1, y1, x2, y2, 'my code')

Now, I would like to insert while fetching the values of my composite foreign keys a,b and c,d in a subquery. Something like this:

INSERT INTO table(a, b, c, d, code) VALUES
((SELECT a, b FROM other-table-1 WHERE ...), 
 (SELECT c, d FROM other-table-2 WHERE ...), 'my code')

The query above doesn't work ofcourse, but it illustrates what I am trying to achieve.

Another try, but also not working (the sub-query has to return one column):

INSERT INTO table(a, b, code)
SELECT (SELECT a, b FROM other-table-1 WHERE ...), 
       (SELECT c, d FROM other-table-2 WHERE ...), 'my code')

Is this somehow possible?

HaveNoDisplayName · Accepted Answer · 2015-08-15 08:12:58Z

1

You have to use the below syntax to insert records, if 'my code' is always is the static

INSERT INTO table(a, b, code)
SELECT a, b, 'my code' FROM other-table WHERE ...

If you have multiple table, then you can use syntax like this using CTE

INSERT INTO table(a, b, c, d, code)
WITH t1 AS (
    SELECT a, b FROM other-table-1 WHERE ...
  ), t2 AS (
    SELECT c, d FROM other-table-2 WHERE ...
  )
select t1.a, t1.b, t2.c, t2.d, 'my code' from t1,t2

edited Aug 15, 2015 at 8:12

answered Aug 15, 2015 at 7:56

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user4460845 Over a year ago

CTE is new for me, I will try it out! Thanks.

HaveNoDisplayName Over a year ago

if you have sample date, then it will be more full-proff solution will be there

user4460845 Over a year ago

I will try it out with meaninful data. If I stumble upon problems, I will open a new question. Your pointer to CTE helped me a lot here, thanks!

edruid · Accepted Answer · 2015-08-15 08:10:16Z

0

Your query should be structuerd something like this:

INSERT INTO table(a, b, c, d, code)
SELECT x.a, x.b, y.c, y.d, 'my code' FROM other-table-1 AS x
CROSS JOIN other-table-2 AS y
WHERE ...

answered Aug 15, 2015 at 8:10

edruid

7234 silver badges15 bronze badges

Collectives™ on Stack Overflow

Postgres - insert and composite foreign keys

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related