title: How do I reference a unique index that uses a function in ON CONFLICT?
body: <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>