INSERT INTO ... RETURNING multiple columns (PostgreSQL)

Question

I've searched around for an answer and it seems definitive but I figured I would double check with the Stack Overflow community:
Here's what I'm trying to do:

INSERT INTO my_table VALUES (a, b, c)
RETURNING (SELECT x, y, z FROM x_table, y_table, z_table
WHERE xid = a AND yid = b AND zid = c)

I get an error telling me I can't return more than one column.
It works if I tell it SELECT x FROM x_table WHERE xid = a.

Is this at all possible in a single query as opposed to creating a seperate SELECT query?

I'm using PostgreSQL 8.3.

corvinusz · Accepted Answer · 2013-12-11 01:20:51Z

16

Try this.

with aaa as (
    INSERT INTO my_table VALUES(a, b, c)
    RETURNING a, b, c)
SELECT x, y, z FROM x_table, y_table, z_table
WHERE xid = (select a from aaa)
  AND yid = (select b from aaa)
  AND zid = (select c from aaa);

In 9.3 similar query works.

answered Dec 11, 2013 at 1:20

corvinusz

5843 silver badges10 bronze badges

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

Comments

Itamar Bitton · Accepted Answer · 2013-12-11 01:49:02Z

5

@corvinusz answer was wrong for 8.3 but gave me a great idea that worked so thanks!

INSERT INTO my_table VALUES (a, b, c)
RETURNING (SELECT x FROM x_table WHERE xid = a),
  (SELECT y FROM y_table WHERE yid = b),
  (SELECT z FROM z_table WHERE zid = c)

I have no idea why the way it's stated in the question is invalid but at least this works.

answered Dec 11, 2013 at 1:49

Itamar Bitton

7862 gold badges7 silver badges26 bronze badges

Comments

pink_daemon · Accepted Answer · 2022-08-15 13:53:27Z

1

I found this approach (within a function!)


DO $$

DECLARE 
    
    returner_ID int;
    returner_Name text;
    returner_Age int;

BEGIN

    INSERT INTO schema.table
        ("ID", "Name", "Age")
    VALUES 
        ('1', 'Steven Grant', '30')
    RETURNING 
        "ID", 
        "Name",
        "Age"
    INTO 
        returner_ID, 
        returner_Name,
        returner_Ag

END; $$

answered Aug 15, 2022 at 13:53

pink_daemon

3943 silver badges6 bronze badges

Collectives™ on Stack Overflow

INSERT INTO ... RETURNING multiple columns (PostgreSQL)

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related