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.

Answer 1

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.