Primary key with ASC or DESC ordering?

Question

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.

• Does PostgreSQL really not allow this?
• Does it ever make sense to have a DESC key like this?
• Is my only option to create an additional index with my desired semantics?

Answer 1

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.