UPSERT

DOCS • LANG

---

The UPSERT statement.

An UPSERT operation is an INSERT operation that automatically converts to an UPDATE operation where the given row data matches an existing record in the table by a unique key. You typically would acheive an UPSERT behaviour over an INSERT statement by means of an ON CONFLICT/ON DUPLICATE KEY clause that explicitly specifies the update instructions. But that isn't always simple. And to add to that, you'd need to explicitly state, in PostgreSQL, over which unique key constraints you want this behaviour to happen.

The whole idea falls right within Linked QL's full awareness of your schema. You can thus simply provide your input data and leave the rest to Linked QL.

The UPSERT statement in Linked QL has the exact same syntax as the INSERT statement but are prohibited from having an ON CONFLICT/ON DUPLICATE KEY clause of their own.

See APIS ➞ client.query(), table.upsert()

Basic Upsert

// (a): SQL syntax
const result = await client.query(
    `UPSERT INTO public.users
        (name, email)
    VALUES ('Jane', 'jane@example.com')`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').upsert({
    data: {
        name: 'Jane',
        email: 'jane@example.com'
    }
});

See related ➞ INSERT