different sort order for primary key index

[Paul Hartley]

I have a composite primary key for a table, let's call it (col1, col2).
When this table is created, obviously an implicit index is created for this
key. I would like the sort order of this index to be different for the two
columns -- if I were to create the index myself, I would pass on (col1, col2
DESC). The ALTER INDEX documentation suggests that it's not possible to
change the sort order of a column, so I can envision two ways to get around
this: 1) create a second UNIQUE index of (col1, col2 DESC), or 2) not
define a primary key and just specify a UNIQUE index separately. Primary
keys are basically restricted to being unique and non-null, but I'm unclear
if PostgreSQL treats primary keys differently from unique, non-null
constraints.

[Albe Laurenz]

Post by Paul Hartley
I have a composite primary key for a table, let's call it
(col1, col2). When this table is created, obviously an
implicit index is created for this key. I would like the
sort order of this index to be different for the two columns
-- if I were to create the index myself, I would pass on
(col1, col2 DESC). The ALTER INDEX documentation suggests
that it's not possible to change the sort order of a column,
so I can envision two ways to get around this: 1) create a
second UNIQUE index of (col1, col2 DESC), or 2) not define a
primary key and just specify a UNIQUE index separately.
Primary keys are basically restricted to being unique and
non-null, but I'm unclear if PostgreSQL treats primary keys
differently from unique, non-null constraints.

I think you can safely go for 2).
Although I admit it is not pretty.

Yours,
Laurenz Albe

[Grzegorz Jaśkiewicz]

there are certain conditions where PK is required, but apart from that it is
pretty much equivalent of unique not null. Obviously index is created, in
order to keep things unique.

the (col1, col2 DESC) type of index is useful, when you have query that uses
it that way. For example, if your query is to search index backwards, it
will be quite slow on some hardware - and adding DESC in index desc, will
make postgresql layout the bits on disc that way - which will obviously
speed things up.

[Tom Lane]

... I'm unclear
if PostgreSQL treats primary keys differently from unique, non-null
constraints.

The *only* thing that the system does specially with a primary key
constraint is that a PK creates a default column target for foreign key
references. For example,

create table m (id int primary key);
create table s (refid int references m);

versus

create table m (id int);
create unique index mi on m (id);
create table s (refid int references m(id));

I have to spell out "(id)" in that last command because there's no PK
to establish a default target.

Other than that, behavior and performance should be the same. The
planner and executor only care about the indexes, not about whatever
constraints they might have come from. Likewise, NOT NULL is NOT NULL
regardless of what syntax you used to slap it onto the column.

regards, tom lane

[Sam Mason]

Post by Tom Lane

... I'm unclear
if PostgreSQL treats primary keys differently from unique, non-null
constraints.

The *only* thing that the system does specially with a primary key
constraint is that a PK creates a default column target for foreign key
references.

It also (silently) overrides any NOT NULL constraint doesn't it? For
example:

CREATE TABLE x ( id INT NULL PRIMARY KEY );

ends up with "id" being NOT NULL, even though I asked for it to be
nullable. Not sure if it's useful for this case to be an error, though
it would be more in line with PG throwing errors when you asked for
something bad instead of making a best guess.

[Grzegorz Jaśkiewicz]

Post by Sam Mason

Post by Tom Lane

... I'm unclear
if PostgreSQL treats primary keys differently from unique, non-null
constraints.

The *only* thing that the system does specially with a primary key
constraint is that a PK creates a default column target for foreign key
references.

It also (silently) overrides any NOT NULL constraint doesn't it? For
CREATE TABLE x ( id INT NULL PRIMARY KEY );
ends up with "id" being NOT NULL, even though I asked for it to be
nullable. Not sure if it's useful for this case to be an error, though
it would be more in line with PG throwing errors when you asked for
something bad instead of making a best guess.
if that happens, shouldn't it be an error ? after all it could potentially

confuse.