Mistakes between an example and its description

El 09/05/18 a las 18:52, PG Doc comments form escribió:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.6/static/sql-createindex.html
Description:

In Postgres Doc: Index
page(https://www.postgresql.org/docs/9.6/static/sql-createindex.html), there
is an mismatch between an example description and an example SQL statement.

The description is below :
To create a B-tree index on the column title in the table films:

Meanwhile, the example provided is an UNIQUE constrain.
CREATE UNIQUE INDEX title_idx ON films (title);

It's not exactly a unique constraint, but it is a unique index, and it
should be mentioned in the title of the documentation.

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Unique Constraint and Unique Index should be the same in this single-column
case, right?

Because, according to CONSTRAINT page in Postgres Documentation, Unique
Constraint on single column is realized as Unique B-TREE index.

I have just realized that it is still a btree index, but I think this
example is not exactly a regular btree example. At least I will put the
example as: CREATE INDEX title_idx ON films USING BTREE(title);

On Wed, May 9, 2018 at 7:59 PM, Martín Marqués <
martin.marques@2ndquadrant.com> wrote:

Eugene Wang wrote:

Unique Constraint and Unique Index should be the same in this single-column
case, right?

Because, according to CONSTRAINT page in Postgres Documentation, Unique
Constraint on single column is realized as Unique B-TREE index.

I have just realized that it is still a btree index, but I think this
example is not exactly a regular btree example. At least I will put the
example as: CREATE INDEX title_idx ON films USING BTREE(title);

Yeah, I see no point for having UNIQUE in that example.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

2018-05-10 16:04 GMT-03:00 Eugene Wang <eugenewangfw@gmail.com>:

Unique Constraint and Unique Index should be the same in this single-column
case, right?

The unique index is what enforces the uniqueness of the field with a
UNIQUE constraint, but a unique index is not necessarily a constraint.

For example, you can use a unique index to create a primary key
constraint with the USING clause, but you can't use the UNIQUE index
created by a UNIQUE CONSTRAINT for this action.

Because, according to CONSTRAINT page in Postgres Documentation, Unique
Constraint on single column is realized as Unique B-TREE index.

I have just realized that it is still a btree index, but I think this
example is not exactly a regular btree example. At least I will put the
example as: CREATE INDEX title_idx ON films USING BTREE(title);

Yes, I agree on dropping the UNIQUE, just like Alvaro. There's another
example which creates a UNIQUE index later in the doc, and not the
best choice for a first example.

Regards,

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Thu, May 10, 2018 at 12:16 PM, Martín Marqués <martin@2ndquadrant.com>
wrote:

2018-05-10 16:04 GMT-03:00 Eugene Wang <eugenewangfw@gmail.com>:

Unique Constraint and Unique Index should be the same in this

single-column

case, right?

The unique index is what enforces the uniqueness of the field with a
UNIQUE constraint, but a unique index is not necessarily a constraint.

​Specifically, a constraint cannot have a WHERE clause while the index can
(i.e., unique indexes can be partial, unique constraints always cover the
entire table).

Thinking this over I'd probably remove UNIQUE from both existing examples.
I'd then add an example of a partial unique index (especially since we lack
an example of a partial index presently) - and note that non-partial unique
indexes are better implemented by defining a constraint on the relation as
opposed to creating the index explicitly.

David J.
​