Support for EXCLUDED in ON CONFLICT DO UPDATE SET

[lorefnon]

Hello, I found that onConflictOn(...).doUpdateSet(...) is available in ts-sql-query, but am not able to find how to use EXCLUDED. Could this be added ?

The intent is to write queries like:

INSERT INTO tags (name, color) 
VALUES ('ts', 'red')
ON CONFLICT ON (name) 
DO UPDATE color = EXCLUDED.color 
RETURNING name, color;

[juanluispaz]

Hi,

That is already supported in ts-sql-query, look the second example in Insert on conflict do update ("upsert")

Example:

const excluded = tCustomer.valuesForInsert()
const insertReturningCustomerData = await connection.insertInto(tCustomer).set({
        firstName: 'John',
        lastName: 'Smith',
        companyId: 1
    })
    .onConflictDoUpdateSet({
        firstName: tCustomer.firstName.concat(' - ').concat(excluded.firstName),
        lastName: tCustomer.lastName.concat(' - ').concat(excluded.lastName)
    })
    .returning({
        id: tCustomer.id,
        firstName: tCustomer.firstName,
        lastName: tCustomer.lastName
    })
    .executeInsertOne()

The generated query in PostgreSQL will be:

insert into customer (first_name, last_name, company_id) 
values ($1, $2, $3) 
on conflict do update set 
    first_name = customer.first_name || $4 || excluded.first_name, 
    last_name = customer.last_name || $5 || excluded.last_name 
returning id as id, first_name as firstName, last_name as lastName

Let me know if that works for you.

[lorefnon]

Oh ok, this is great. Thank you.