Thread: Mistakes between an example and its description
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);
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.
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:
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
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
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.