Thread: B-tree + sorting + unique constraint

Hi, I have a follow-up question to my earlier question[1] about how
sorting works in an index.

Does creating a UNIQUE constraint not allow the aforementioned sorting
capability?

eg.

create table t (x int, y int, z int);
insert into t values (1,1,1),(2,2,2),(3,3,3),(1,2,3),(15,23,21);

-- works:
create unique index t_uidx on t (x desc nulls last,y desc nulls last, z asc);
drop index t_uidx;

-- works:
alter table t add constraint t_uc unique (x,y,z);
alter table t drop constraint t_uc;

-- creating the unique constraint with sorting fails:
alter table t add constraint t_xyz_uc unique (x desc nulls last,y desc
nulls last, z asc);

ERROR:  syntax error at or near "desc"
LINE 1: alter table t add constraint t_uidx unique (x desc nulls las...



[1] - http://archives.postgresql.org/pgsql-general/2010-12/msg00959.php

On Wed, 2010-12-29 at 16:39 -0800, bricklen wrote:
> -- works:
> create unique index t_uidx on t (x desc nulls last,y desc nulls last, z asc);
> drop index t_uidx;

...

> -- creating the unique constraint with sorting fails:
> alter table t add constraint t_xyz_uc unique (x desc nulls last,y desc
> nulls last, z asc);
>
> ERROR:  syntax error at or near "desc"
> LINE 1: alter table t add constraint t_uidx unique (x desc nulls las...

A UNIQUE constraint in a table definition only allows simple column
references. To get more advanced, you need to use the CREATE UNIQUE
INDEX syntax you showed above.

Regards,
    Jeff Davis

On Wed, Dec 29, 2010 at 5:04 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Wed, 2010-12-29 at 16:39 -0800, bricklen wrote:
>> -- works:
>> create unique index t_uidx on t (x desc nulls last,y desc nulls last, z asc);
>> drop index t_uidx;
>
> ...
>
>> -- creating the unique constraint with sorting fails:
>> alter table t add constraint t_xyz_uc unique (x desc nulls last,y desc
>> nulls last, z asc);
>>
>> ERROR:  syntax error at or near "desc"
>> LINE 1: alter table t add constraint t_uidx unique (x desc nulls las...
>
> A UNIQUE constraint in a table definition only allows simple column
> references. To get more advanced, you need to use the CREATE UNIQUE
> INDEX syntax you showed above.
>
> Regards,
>        Jeff Davis

Ok, thanks for your reply. I think I'll have to reread the docs to get
a better feel for those little nuances.