1

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
  • Seems like a duplicate of dba.stackexchange.com/questions/50693 ? But returning a cross-join of source and destination is useless in general. Hopefully someone will come up with a more comprehensive answer. – Daniel Vérité Jan 10 at 20:34
  • Your question is a little ambiguous. If you mean you want to additionally return columns of the source table, please state that explicitly in your description. As it currently stands, your question could be understood as though it's based on a false premise, because you actually can return other columns of the target table than the ones you have explicitly specified in the insert statement. For instance, if customers2 had column date_added populated automatically with a default, you could specify it in the returning clause even if you were not explicitly inserting values into it. – Andriy M Jan 11 at 1:43
0

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

  • This differs from the question in details that matter: it inserts only one row, and t2 lacks a column that is not inserted into t1. If you add these, it doesn't look like it addresses the problem. – Daniel Vérité Jan 10 at 20:47
  • @DanielVérité if the question is: Can you get columns from other tables than the affected by the INSERT command?, the answer is no, you can't. The nested cte could solve the OP issue. – McNets Jan 10 at 21:32
  • Ok, so this sort of works well if there is a natural key on the data (in my case, "name"). Then I can write with y as (select name, email, active from customers ) ,x as ( INSERT INTO customers2 (NAME, email) select name, email from customers returning customer_id, name, email ) select y.*, x.customer_id from x, y where x.name = y.name ; – thatdataguy Jan 11 at 14:24
  • Yes, or FROM x JOIN y ON x.name = y.name. But keep in mind you're inserting only one row, you could avoid it. – McNets Jan 11 at 14:31
  • I'm going to be inserting many, many rows. The above was just a simple example of the pattern I want to write. – thatdataguy Jan 11 at 14:44

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.