CODEWITHSUNDEEP
postgresql
Mike Curtiss
Mike Curtiss

Reputation: 1938

Primary key with ASC or DESC ordering?

I'm trying to create a table with a compound primary key where the second column is ordered descending:

CREATE TABLE AccountHistory (                                                                                                                                                                           
  AccountNumber BIGINT NOT NULL,                                                                                                                                                                        
  Ts TIMESTAMP NOT NULL,                                                                                                                                                                                
  Memo TEXT,                                                                                                                                                                                            
  ChangeAmount BIGINT NOT NULL,                                                                                                                                                                         
  PRIMARY KEY (AccountNumber, ts DESC)                                                                                                                                                              
);

However, PostgreSQL is saying there's a syntax error at the DESC clause.

Upvotes: 9

Views: 15172

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324511

I think it would be reasonable to do that, as semantically an index in ascending or descending order is the same, but PostgreSQL does not support it. There's no way to control the index order of an index that is auto-created to back a primary key.

PostgreSQL won't let you create one by creating the index manually as a UNIQUE index with DESC sorting order then creating a declared PRIMARY KEY constraint with it using ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX .... It will fail with:

ERROR:  index "foopk" does not have default sorting behavior

I do not know off the top of my head why Pg requires this. Searching the source code for the above error would probably find you a suitable comment.

You can get PRIMARY KEY-like behaviour without the constraint metadata just by creating the unique index separately. That might be OK for you.

Upvotes: 6

cske
cske

Reputation: 2243

See https://dba.stackexchange.com/questions/90722/is-unique-index-better-than-unique-constraint-when-an-index-with-an-operator-cla

You can create an index like

CREATE UNIQUE INDEX accounthistory_pk_2 on AccountHistory(AccountNumber, ts DESC);

but that cannot be PRIMARY KEY on table, although is important for queries like 

select DISTINCT on (accountnumber) * from AccountHistory
order by accountnumber,ts desc;

Test:

CREATE TABLE AccountHistory (
  AccountNumber BIGINT NOT NULL,
  Ts TIMESTAMP NOT NULL,
  Memo TEXT,
  ChangeAmount BIGINT NOT NULL
);

EXPLAIN select DISTINCT on (accountnumber) * from AccountHistory
order by accountnumber,ts desc;

"Unique  (cost=65.82..70.52 rows=200 width=56)"
"  ->  Sort  (cost=65.82..68.17 rows=940 width=56)"
"        Sort Key: accountnumber, ts"
"        ->  Seq Scan on accounthistory  (cost=0.00..19.40 rows=940 width=56)"

set enable_seqscan=false;

"Unique  (cost=10000000065.82..10000000070.52 rows=200 width=56)"
"  ->  Sort  (cost=10000000065.82..10000000068.17 rows=940 width=56)"
"        Sort Key: accountnumber, ts"
"        ->  Seq Scan on accounthistory  (cost=10000000000.00..10000000019.40 rows=940 width=56)"

CREATE UNIQUE INDEX accounthistory_pk_1 on AccountHistory(AccountNumber, ts);

"Unique  (cost=10000000065.82..10000000070.52 rows=200 width=56)"
"  ->  Sort  (cost=10000000065.82..10000000068.17 rows=940 width=56)"
"        Sort Key: accountnumber, ts"
"        ->  Seq Scan on accounthistory  (cost=10000000000.00..10000000019.40 rows=940 width=56)"

CREATE UNIQUE INDEX accounthistory_pk_2 on AccountHistory(AccountNumber, ts DESC);

"Unique  (cost=0.15..60.60 rows=200 width=56)"
"  ->  Index Scan using accounthistory_pk_2 on accounthistory  (cost=0.15..58.25 rows=940 width=56)"

Upvotes: 4

Related Questions