Where can I find more info about the `UNIQUE (unique DESC)` sql syntax?

[adriancuadrado]

I was reading this article when I stumbled upon this SQL:

CREATE TABLE `Unique` (
  `unique` INT,
  CONSTRAINT `Unique_unique_key` UNIQUE (`unique` DESC)
)

I didn't know you could specify (`unique` DESC), but when I went to do some research about it I couldn't find any documentation in Postgresql.

[cookieMonsterDev]

This probably a mistake in docs constraints cannot specify sort order (ASC/DESC), only indexes can.

Usage of CONSTRAINT should be like:

CREATE TABLE `Unique` (
  `unique` INT,
  CONSTRAINT `Unique_unique_key` UNIQUE (`unique`)
)

If you need sort order, do so:

CREATE TABLE `Unique` (
  `unique` INT
);

CREATE UNIQUE INDEX unique_unique_key ON `Unique` (`unique` DESC);

Sources:

• Unique Constraints
• Unique Indexes

[adriancuadrado]

@cookieMonsterDev

This probably a mistake in docs constraints cannot specify sort order (ASC/DESC), only indexes can.

Usage of CONSTRAINT should be like:

CREATE TABLE `Unique` (
  `unique` INT,
  CONSTRAINT `Unique_unique_key` UNIQUE (`unique`)
)

If you need sort order, do so:

CREATE TABLE `Unique` (
  `unique` INT
);

CREATE UNIQUE INDEX unique_unique_key ON `Unique` (`unique` DESC);

Sources:

• Unique Constraints
• Unique Indexes

According to the Unique Indexes link, the syntax for unique indexes is as follows:

CREATE UNIQUE INDEX name ON table (column [, ...]) [ NULLS [ NOT ] DISTINCT ];

As you can see, there is no DESC there either.

[cookieMonsterDev]

@adriancuadrado Try look into this: Indexes and ORDER BY

[jharrell]

Yup this is most likely errata. Thanks @adriancuadrado and @cookieMonsterDev for the correct examples. I’ll get this fixed.