How do I reference a unique index that uses a function in ON CONFLICT?
<p>I'm using postgres 9.5.3, and I have a table like this:</p>

<pre><code>CREATE TABLE packages (
  id   SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL
);
</code></pre>

<p>I have defined a function, <code>canonical_name</code>, like this:</p>

<pre><code>CREATE FUNCTION canonical_name(text) RETURNS text AS $$
    SELECT replace(lower($1), '-', '_')
$$ LANGUAGE SQL;
</code></pre>

<p>I've added a unique index to this table that uses the function:</p>

<pre><code>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))
</code></pre>

<p>And this unique index is working as I expect; it prevents insertion of duplicates:</p>

<pre><code>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.
</code></pre>

<p>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. <a href="https://www.postgresql.org/docs/current/static/sql-insert.html">The documentation</a> seems to say I can specify an index expression:</p>

<pre><code>where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
</code></pre>

<p>But all of these things below that I've tried produce errors as shown, instead of a working upsert.</p>

<p>I've tried matching the index expression as I specified it:</p>

<pre><code>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
</code></pre>

<p>Matching the index expression as <code>\d+</code> showed it:</p>

<pre><code>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
</code></pre>

<p>Just naming the column that the unique index is on:</p>

<pre><code>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
</code></pre>

<p>Using the index name instead:</p>

<pre><code>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)
</code></pre>

<p>So how <em>do</em> I specify that I want to use this index? Or is this a bug?</p>