Question 90381 in PostgreSQL Question posted in
The official documentation can be found here.

Returning an array of inserted sequence values – Postgresql

I have an insert statement that may insert single or multiple records into a table with an INSERT statement. I need to know what the primary id value is for each new row.

Tried adding the following to the insert statement:

RETURNING array_agg(new_seq_id) INTO new_ids

Get the following error:

[42803] ERROR: aggregate functions are not allowed in RETURNING

Is there a way to get an array into the returning statements of all the new sequence values?

Tags:

3

Answers


  1. 0

    You need to wrap it into a common table expression:

    with new_rows as (
  insert into (...)
  values (...)
  returning new_seq_id
)
select array_agg(new_seq_id)
  into new_ids
from new_rows;
    Login or Signup to reply.
  2. 0

    Yes, you can use a data modifying CTE.

    with t as
(
  -- your insert statement
  RETURNING new_seq_id
) 
select array_agg(new_seq_id) from t into new_ids;
    Login or Signup to reply.
  3. 0

    After returning newly generated (and actually inserted!) IDs in a data-modifying CTE (like others provided), to insert into a table, you need an INSERT statement:

    WITH cte AS (
   INSERT ...
   RETURNING new_seq_id
   )
INSERT INTO tbl(new_ids)
SELECT ARRAY(TABLE cte)

    TABLE cte is the short form of SELECT * FROM cte.

    An ARRAY constructor is typically a bit faster than the (more versatile) array_agg() aggregate function. See:

    Only makes sense if you, indeed, need to return a single array. You might just return a set of rows with the RETURNING clause.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search