Return inserted values from 'insert' query

Question

I have simple table and I need to insert in to it values from array, but only values which are not exists in table and return from query inserted values, how I can do this? I have next query, but it just inserts values:

INSERT INTO my_table(id, card_id, size) 
    VALUES ${myArray.map(item => `($${addDbValue(item.id, dbValues)}, 
    $${dbValues.push(item.card_id)}::int, '24')`)}
     `, dbValues)

unique is card_id

score 1 · Accepted Answer · answered Sep 10 '20 at 16:19

You seem to want on conflict and returning:

insert into my_table(id, card_id, size) 
values (?, ?, ?)
on conflict (card_id) do nothing
returning *

The query inserts the new row, and returns the entire row (including columns that were not initially given for insert). If a row already exists with the same card_id, the insert is not performed (for this to work, you need a unique index or constraint on card_id).

Note that you should be using query parameters (as shown above) rather than concatenating variables in the query string. Have a look at the parameterized query feature of your client.

Return inserted values from 'insert' query

1 Answers1