Comment 3 for bug 1695911

My first attempt to create a unique index over our data with truncated values was:

CREATE UNIQUE INDEX CONCURRENTLY browse_entry_sort_value_value_key ON metabib.browse_entry (SUBSTRING(sort_value FROM 0 FOR 2048), SUBSTRING(value FROM 0 FOR 512));

This failed with a duplicate of a super-long title, thanks to Eighteenth Century Collections Online (ECCO):

ERROR: could not create unique index "browse_entry_sort_value_value_key"
DETAIL: Key ("substring"(sort_value, 0, 2048), "substring"(value, 0, 512))=(a compleat history of europe or a view of the affairs thereof civil and military for the year 1705 containing all the publick and secret transactions therein the several steps taken by france for an universal monarchy and to enslave her neighbours the wars in italy poland germany netherlands spain &c intermixd with great variety of original papers letters memoirs treaties &c several of which never before made publick with the remarkables of the year including particularly the lives of several eminent persons both at home and abroad that died therein and correc lists of persons in offices or places of trust in her majestys government illustrated with maps to be continued annually, A compleat history of Europe or, a view of the affairs thereof, civil and military, for the year, 1705. Containing all the publick and secret transactions therein; The several Steps taken by France, for an Universal Monarchy, and to Enslave her Neighbours; The Wars in Italy, Poland, Germany, Netherlands, Spain, &c. Intermix'd with great variety of original papers, Letters, Memoirs, Treaties, &c. Several of which never before made Publick. With the remarkables of the year; including particularly the Lives o) is duplicated.

My next attempt added the metabib.browse_entry.id column to the unique index to guarantee uniqueness:

CREATE UNIQUE INDEX CONCURRENTLY browse_entry_sort_value_value_key ON metabib.browse_entry (SUBSTRING(sort_value FROM 0 FOR 2048), SUBSTRING(value FROM 0 FOR 512), id);

Success! But then trying to add the unique constraint failed:

ALTER TABLE metabib.browse_entry ADD CONSTRAINT browse_entry_sort_value_value_key UNIQUE USING INDEX browse_entry_sort_value_value_key;
ERROR: index "browse_entry_sort_value_value_key" contains expressions
LINE 1: ALTER TABLE metabib.browse_entry ADD CONSTRAINT browse_entry...
                                             ^
DETAIL: Cannot create a primary key or unique constraint using such an index.

This is in accordance with https://www.postgresql.org/docs/9.4/static/sql-altertable.html in the section "ADD table_constraint_using_index" that states:
    This form adds a new PRIMARY KEY or UNIQUE constraint to a table based on an existing unique index.

    The index cannot have expression columns nor be a partial index.

So... not sure how to proceed.

(For what it's worth, the only difference between the two metabib.browse_entry rows is that the value of one of the rows has an uppercase 'P' at char position 530 vs. lowercase 'p' in the other row - thus outside of the arbitrary truncation limit, but resulting in an identical sort_value).