B-tree + sorting + unique constraint

From: bricklen <bricklen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: B-tree + sorting + unique constraint
Date: 2010-12-30 00:39:55
Message-ID: AANLkTinffreG=jpy1kG9Y3=iKHtE=PgUWM4u=8C6DTkL@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2010-12-30 01:04:14 Re: B-tree + sorting + unique constraint
Previous Message Gary Fu 2010-12-29 22:19:40 what's the message: CONTEXT: PL/pgSQL function "proc_sub_b" line 7 at assignment