Is it possible to return an array of ids from a Postgresql insert statement within the returning clause?

user_1964636 0
Viewed 868

I am trying to write a delete statement, and the returning clause returns the ids as an array of objects as such:

[
{simcard_id: 1},
{simcard_id: 2},
{simcard_id: 3},
]

but I would like it to return an array of numbers as such:

[1, 2, 3]

Is there a way I can have the statement return an array of just numbers? I tried a few aggregate functions but I keep getting errors saying I can't do array_agg functions in a returning statement. I also tried to doing this:

RETURNING ARRAY[simcard_id]

But then it just got weird.

I'm not the best at sql so any help would be greatly appreciated.

I know I can just map through the values and return an array of numbers but I would like to take this opportunity to learn if I can do it using sql.

Here is the sql query. It is using the postgres library for nodejs.

      const simcardsIds = await sql`
      DELETE FROM simcards
      WHERE simcard_id = ANY(${sql.array(simcards)})
      RETURNING simcard_id
      `
sql-returning sql-insert postgresql sql javascript
1 Answers

You can get close to that using something like this:

with x as (
   delete from simcards
   where simcard_id in (1,2)
   returning simcard_id
)
select array_agg(simcard_id) as y from x;

That'll give you output such as this:

 y
-----------
 {1,2}

You can go a bit farther like so:

with x as (
    delete from simcards
    where simcard_id in (1,2)
    returning simcard_id
),
main as (select array_agg(simcard_id) as y from x)
select replace(  replace(y::text, '{', '['),   '}', ']') as out from main;

That'll give you:

  out  
-------
 [1,2]
user_25545370
Related