Upsert

Subtopics

EdgeQL Tutorial

Upsert

The "upsert" is only one possible use of the conditional INSERT command. In reality there are several other useful usages of UNLESS CONFLICT construct. In its most basic form, it allows to simply ignore conflicting INSERT commands rather than causing an error. This is useful if we want the INSERT to be idempotent, such as in the case of trying to create composable initialization scripts. Here's an example of such an INSERT:

Input

INSERT User {
    name := 'Idempotent User',
    email := 'idempotent.user@edgedb.com'
} UNLESS CONFLICT;

INSERT User {
    name := 'Idempotent User',
    email := 'idempotent.user@edgedb.com'
} UNLESS CONFLICT;

Run

Output

Press the 'Run' button to evaluate the input

Input

INSERT User {
    name := 'Idempotent User',
    email := 'idempotent.user@edgedb.com'
} UNLESS CONFLICT;

INSERT User {
    name := 'Idempotent User',
    email := 'idempotent.user@edgedb.com'
} UNLESS CONFLICT;

Run

Output

Press the 'Run' button to evaluate the input

Input

INSERT User {
    name := 'Idempotent User',
    email := 'idempotent.user@edgedb.com'
} UNLESS CONFLICT ON .email;

INSERT User {
    name := 'Idempotent User',
    email := 'idempotent.user@edgedb.com'
} UNLESS CONFLICT ON .email;

Run

Output

Press the 'Run' button to evaluate the input

Firstly, notice that the most basic form of UNLESS CONFLICT has no condition or an ELSE part.

Secondly, see that only the first INSERT produced an object, while the following ones resulted in empty sets.

Lastly, we encourage you to experiment and see what happens if UNLESS CONFLICT is omitted in the above example.

Prev Next