Is unique index better than unique constraint when an index with an operator class is required
For a moment I thought one might be able to use a pre-existing  text_pattern_ops index with the USING INDEX clause when adding a UNIQUE CONSTRAINT. But that fails, because:


ERROR: index "book2_name_like" does not have default sorting behavior



Per documentation:


  The index cannot have expression columns nor be a partial index. Also,
  it must be a b-tree index with default sort ordering. These
  restrictions ensure that the index is equivalent to one that would be
  built by a regular ADD PRIMARY KEY or ADD UNIQUE command.


For instance, a unique index like that would allow a FK constraint referencing it, but perform terribly, because it does not support standard operators.
Per documentation:


  Note that you should also create an index with the default operator
  class if you want queries involving ordinary &lt;, &lt;=, &gt;, or &gt;= comparisons to use an index.


So to answer the question:

If you need a UNIQUE CONSTRAINT (among other reasons: to reference it with a FK), your first variant with constraint and index is the only option. Additionally, the default operator class of the index created by the constraint supports more operations (like sorting in default sort order).

If you don't need any of that go with your second variant because, obviously, just one index is cheaper to maintain: just a UNIQUE text_pattern_ops index.

Differences between index and constraint:


How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?


Alternative with COLLATE "C"

Instead of creating two indexes, there is another alternative for xxx_pattern_ops indexes that may be preferable. The documentation:


  The difference from the default operator classes is that the values
  are compared strictly character by character rather than according to
  the locale-specific collation rules. This makes these operator
  classes suitable for use by queries
  involving pattern matching expressions (LIKE or POSIX regular
  expressions) when the database does not use the standard "C" locale.


And:


  The index automatically uses the collation of the underlying column.


You can create the column without collation (using COLLATE "C"). Then the default operator class behaves the same way as text_pattern_ops would - plus the index can be used with all the standard operators.

CREATE TABLE book2 (
   book_id serial PRIMARY KEY,
   book    text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

Now, LIKE can use the index:

SELECT * FROM book2 WHERE book LIKE 'foo%';


But ILIKE still can't:

SELECT * FROM book2 WHERE book ILIKE 'foo%';


SQL Fiddle.

Consider a trigram index using the additional module pg_trgm for a more general solution.:


Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL