GitLab

Summary

Postgres unique indices support expressions (like lower(name)). It seems currently impossible to use such constraints in conjunction with massive's insert onConflictUpdate because it automatically puts quotes around the fields.

Example

Database setup

CREATE TABLE things (
  id SERIAL PRIMARY KEY,
  stuff TEXT,
  name TEXT
);

CREATE UNIQUE INDEX CONCURRENTLY stuff_name_idx
ON things (stuff, lower(name));

Code demonstrating the behavior

db.things.insert({ stuff: 'stuff', name: 'thing' });
db.things.insert({ stuff: 'stuff', name: 'Thing' }, { onConflictUpdate: ['stuff', 'lower(name)'] });

Expected behavior

A conflict causes the thing to be updated.

Actual behavior

An error occurs, stating that "lower(name)" is not a column of the things table:

InternalServerError: column "lower(name)" does not exist

Additional context

We were able to work around this by using this hack:

db.things.insert({ stuff: 'stuff', name: 'Thing' }, { onConflictUpdate: ['stuff", lower(name), "stuff'] });