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 your update list. 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.

An UPSERT statement in Linked QL follows the exact same syntax of an INSERT statement but are prohibited from having an explicit ON CONFLICT/ON DUPLICATE KEY clause.

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(
    { name: 'Jane', email: 'jane@example.com' }
);

Multi-Dimensional Upserts

See ➞ Magic Paths