Inserting by name rather than position in Postgres

Question

In Postgres (version 9.4 fwiw), an INSERT INTO ... SELECT query seems to insert based on column position rather than name:

pg=# CREATE TEMPORARY TABLE x (a int, b int);
CREATE TABLE
pg=# INSERT INTO x (a, b) SELECT 1 as b, 2 as a;
INSERT 0 1
pg=# SELECT * FROM x;
 a | b
---+---
 1 | 2
(1 row)

Is there any way to structure this query or something similar such that the columns would be inserted by name rather than positionally? I would want the result here to be:

pg=# SELECT * FROM x;
 a | b
---+---
 2 | 1
(1 row)

The context here is that the contents of the SELECT are being defined in a different place than the overall INSERT call, so it's difficult to enforce a common order.

There is no way to do this in SQL.

Laurenz Albe
– Laurenz Albe

2017-02-07 15:35:23 +00:00
Commented Feb 7, 2017 at 15:35 — Laurenz Albe
– Laurenz Albe, Commented Feb 7, 2017 at 15:35
@LaurenzAlbe check out the clever answer below!

nicolaskruchten
– nicolaskruchten

2017-02-08 12:26:25 +00:00
Commented Feb 8, 2017 at 12:26 — nicolaskruchten
– nicolaskruchten, Commented Feb 8, 2017 at 12:26

Thorsten Kettner · Accepted Answer · 2017-02-08 08:22:43Z

1

Simply move the query to a subquery:

INSERT INTO x (a, b) SELECT a, b FROM (SELECT 1 AS b, 2 AS a) src;

answered Feb 8, 2017 at 8:22

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

1 Comment

nicolaskruchten Over a year ago

Ah, that's a great solution! Thank you :)

JohnHC · Accepted Answer · 2017-02-07 15:32:58Z

0

You define the order of the columns with the insert statement:

INSERT INTO x (b, a) 
SELECT 1 as b, -- maps to first column above
       2 as a; -- maps to second column above

answered Feb 7, 2017 at 15:32

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

2 Comments

nicolaskruchten Over a year ago

Right, this much is clear, but I want this to work even if the order between the two parts of the query don't match... I'll clarify the question.

JohnHC Over a year ago

@nicolaskruchten I see what you're saying... Bottom line is, with an insert ... select, the values in the select will insert in the order defined in the parentheses of the INSERT

Collectives™ on Stack Overflow

Inserting by name rather than position in Postgres

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related