Postgresql – Return more columns than were inserted inside a PostgreSQL CTE

postgresql

I would like to use a modifying CTE to insert some values. The insert statement inserts data from a select statement. I use the returning keyword to return the values inserted (including auto-incrementing columns). However, I also want that CTE to return other columns. How can I do this?

An example follows:

drop table if exists customers;

CREATE TABLE customers (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL,
 active bool NOT NULL DEFAULT TRUE
);


INSERT INTO customers (NAME, email)
VALUES
 ('IBM', 'contact@ibm.com'),
 (
 'Microsoft',
 'contact@microsoft.com'
 ),
 (
 'Intel',
 'contact@intel.com'
 );

drop table if exists customers2;

CREATE TABLE customers2 (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL
);


with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x
;

I would like the last statement to return columns customer_id, name, email, active. But I get an error:

Error: ERROR: column "active" does not exist
  Position: 123
SQLState:  42703
ErrorCode: 0
Error occurred in:
with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x

Best Answer

According Postgres Docs about 6.4. Returning Data From Modified Rows you can't.

Quoted from docs:

In an INSERT, the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values. For example, when using a serial column to provide unique identifiers, RETURNING can return the ID assigned to a new row.

Unless this data is updated by a trigger:

If there are triggers (Chapter 39) on the target table, the data available to RETURNING is the row as modified by the triggers. Thus, inspecting columns computed by triggers is another common use-case for RETURNING.

You can check it by using RETURNING *

CREATE TABLE t1 (id int, foo int);
CREATE TABLE t2 (id int, bar int);

INSERT INTO t2 VALUES (1, 2);

INSERT INTO t1 (id, foo)
SELECT id, bar
FROM   t2
WHERE  id = 1
RETURNING *;

id | foo
-: | --:
 1 |   2

db<>fiddle here

You could get it by using a nested CTE:

WITH x AS
(
    SELECT * FROM t2 WHERE id = 1
), y AS
 (
    INSERT INTO t1 (id, foo)
    SELECT id, bar
    FROM   x
    RETURNING *
 )
 SELECT x.*, y.*
 FROM x, y;

id | bar | id | foo
-: | --: | -: | --:
 1 |   2 |  1 |   2

db<>fiddle here

Related Question