I'm using postgres 9.5.3, and I have a table like this:
CREATE TABLE packages (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
I have defined a function, canonical_name
, like this:
CREATE FUNCTION canonical_name(text) RETURNS text AS $$
SELECT replace(lower($1), '-', '_')
$$ LANGUAGE SQL;
I've added a unique index to this table that uses the function:
CREATE UNIQUE INDEX index_package_name
ON packages (canonical_name(name));
CREATE INDEX
# \d+ packages
Table "public.packages"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-------------------+-------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('packages_id_seq'::regclass) | plain | |
name | character varying | not null | extended | |
Indexes:
"packages_pkey" PRIMARY KEY, btree (id)
"index_package_name" UNIQUE, btree (canonical_name(name::text))
And this unique index is working as I expect; it prevents insertion of duplicates:
INSERT INTO packages (name)
VALUES ('Foo-bar');
INSERT INTO packages (name)
VALUES ('foo_bar');
ERROR: duplicate key value violates unique constraint "index_package_name"
DETAIL: Key (canonical_name(name::text))=(foo_bar) already exists.
My problem is that I want to use this unique index to do an upsert, and I can't figure out how I need to specify the conflict target. The documentation seems to say I can specify an index expression:
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
But all of these things below that I've tried produce errors as shown, instead of a working upsert.
I've tried matching the index expression as I specified it:
INSERT INTO packages (name)
VALUES ('foo_bar')
ON CONFLICT (canonical_name(name))
DO UPDATE SET name = EXCLUDED.name;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Matching the index expression as \d+
showed it:
INSERT INTO packages (name)
VALUES ('foo_bar')
ON CONFLICT (canonical_name(name::text))
DO UPDATE SET name = EXCLUDED.name;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Just naming the column that the unique index is on:
INSERT INTO packages (name)
VALUES ('foo_bar')
ON CONFLICT (name)
DO UPDATE SET name = EXCLUDED.name;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Using the index name instead:
INSERT INTO packages (name)
VALUES ('foo_bar')
ON CONFLICT (index_package_name)
DO UPDATE SET name = EXCLUDED.name;
ERROR: column "index_package_name" does not exist
LINE 3: ON CONFLICT (index_package_name)
So how do I specify that I want to use this index? Or is this a bug?
Important note: This behavior can only be observed on versions before 9.5.4. This is a bug that was fixed in 9.5.4. The rest of the answer describes the buggy behavior:
As you found out, you can only specify the expression for a unique constraint and not the one for a unique index. This is somewhat confusing because under the hood a unique constraint is just a unique index (but that is considered an implementation detail).
To make matters worse for you, you cannot define a unique constraint over a unique index that contains expressions – I am not certain what the reason is, but suspect the SQL standard.
One way you can do this would be to add an artificial column, filled with the “canonical name” by a trigger and define the constraint on that column. I admit that that is not nice.
The correct solution, however, is to upgrade to the latest minor release for PostgreSQL 9.5.