Is unique index better than unique constraint when an index with an operator class is required

Question

Postgres docs say:

The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

(削除) Based on this, if I want an expression index on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a lower case index?

As @Colin'tHart pointed out, these 2 cases aren't the same. I should have posted this question without the use of lower() expression. In that case, my understanding is that a CREATE UNIQUE INDEX would be better than a unique constraint and a simple index. (削除ここまで)

Based on this, if I want an index with an operator class (e.g. text_pattern_ops) on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a different operator class?

Case 1:

CREATE TABLE book (
 id SERIAL PRIMARY KEY,
 name text NOT NULL,
 CONSTRAINT book_name_key UNIQUE (name)
);
CREATE INDEX book_name_like ON book (name text_pattern_ops);

Case 2:

CREATE TABLE book (
 id SERIAL PRIMARY KEY,
 name text NOT NULL
);
CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);

• Daniel Vérité: But case 2 prevents 'Foo' and 'foo' to coexist, whereas case 1 doesn't prevent it. They don't accomplish exactly the same thing.
• Daniel Vérité: Presumably you want your case 1 book_name_like index to be UNIQUE too, and then the unique CONSTRAINT on name alone should indeed go away.
• Erwin Brandstetter: What do you mean by "unique constraint and a simple index"? You already have a simple index with the unique constraint. And you cannot have a unique constraint for an expression. so there is really no overlap here. Or I am missing your point? For differences between unique index and constraint: stackoverflow.com/questions/9066972/…
• user4150760: @ErwinBrandstetter : Agreed, the question is confusing. I've tried to clarify it further. The ORM I use maps a unique constraint to a unique constraint plus an explicit index (using pattern ops). I feel that it's adding an extra index
• Erwin Brandstetter: The updated question makes a lot more sense.

Answer 1

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

The manual:

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.
The manual:

Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= 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 manual:

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%';

db<>fiddle here _{Old sqlfddle}

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

• Pattern matching with LIKE, SIMILAR TO or regular expressions

Answer 2

Be careful.

Your two designs are not the same.

Case 1 implements a unique constraint on the name of the book but includes case, so "The Lord of the Flies" and "the lord of the flies" would be different. Case 1 then creates a second index to support efficient searching of queries of the form

select *
from book
where lower(name) like 'the lord%';

Case 2 is different. It forces your book titles to be different regardless of case and wouldn't allow both of the above titles to be inserted.

• Aaron Bertrand: In SQL Server, this would only be true in a case sensitive collation (and we don't have expression-based indexes). I'll be honest, I missed the lower() over on the right (pretty narrow code window on mobile).
• user4150760: @Colin'tHart : Correct, didn't realize that. I guess my conclusion (about case 2) would be meaningful if I don't need an expression index i.e. Just a unique constraint and a simple index.
• Colin 't Hart: The OP isn't asking about SQL Server, @AaronBertrand.
• Aaron Bertrand: Thanks, I comprehended that. I guess my implied question was missed: does PG treat only (expression-based) unique indexes treated as case sensitive?
• András Váczi: @AaronBertrand no, it definitely does not. You can sleep well :)