Thread: different sort order for primary key index

Paul Hartley wrote:
> 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

Paul Hartley <phartley@gmail.com> writes:
> ... 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

On Wed, Oct 14, 2009 at 10:29:56AM -0400, Tom Lane wrote:
> Paul Hartley <phartley@gmail.com> writes:
> > ... 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.

--
  Sam  http://samason.me.uk/