Postgresql : sorted order on a multicolumn primary key

Question

Consider the following table definition in a PostgreSql database :

CREATE TABLE data (
    id bigint NOT NULL, 
    updateRound timestamp WITH time zone NOT NULL
);

CREATE UNIQUE INDEX idx_unique_data ON data (id, updateRound DESC);
ALTER TABLE data ADD CONSTRAINT pk_data PRIMARY KEY (id, updateRound);

This code creates 2 indexes while 1 should be enough. However, I cannot add a sorted order on the primary key definition. And I have a guilty conscience if I leave a table without primary key.

What should be the best approach ?

Edit : PostgreSql multicolumn index ordering for reference : https://www.postgresql.org/docs/current/static/indexes-ordering.html

Edit 2 : good explanations in Primary key with ASC or DESC ordering? . However, I know PostgreSql does not accept sorting on the primary key constraint : this can only be done on an index. But when it comes to implementation, PostgreSql produces 2 indexes for the above definition. I wish the first index could be re-used by the primary key constraint.

Possible duplicate of Primary key with ASC or DESC ordering? — philipxy
A PK constrains to UNIQUE NOT NULL but supplies a default for FKs to the table. There is no relational theory reason for PKs. See my answer at the duplicate link. PS (We can obviously expect that) This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. — philipxy

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-09-14T13:58:45

I guess you need that index to support an ORDER BY, right? I cannot think of a WHERE condition that would require the second column in descending sort order.

Since both columns are defined NOT NULL, the unique index will behave just like a primary key constraint, and it can be used as the target of foreign key constraints.

So unless you have a tool that checks for constraints and fails if it cannot find any, or you need the constraint for an INSERT ... ON CONFLICT, having the index is good enough in my opinion.

Postgresql : sorted order on a multicolumn primary key

1 Answers