11

Postgresql allows you to define a primary key using a group of columns instead of one if desired, like so:

PRIMARY KEY(a_id, b_id)

But is the order of the columns in this definition significant? Is there any practical or actual difference between the above and the below:

PRIMARY KEY(b_id, a_id)

The documentation does not address this directly. It implies that the order should not be significant, and when I query the information_catalog tables I do not see anything to note the difference in parameter order.

But using the \d command on two tables using the alternate definitions and the order of the columns as described by the output does change. Apparently there is something in pg_catalog which retains the order of the primary key definition, but does this imply there is any functional difference at all between the two definitions?

Thank you

1
  • Have you done a simple experiment to find the answer? It isn't hard to find out for yourself. May 21, 2015 at 19:54

1 Answer 1

13

But is the order of the columns in this definition significant? Is there any practical or actual difference between the above and the below:

The order of columns in the PRIMARY KEY is significant in that the underlying UNIQUE b-tree index is created with the attributes in that order, and that affects index lookup efficiency. You generally want the most selective attributes first. This only affects performance, it doesn't change the meaning of queries.

There is no semantic difference, because UNIQUE (a, b) implies UNIQUE (b,a).

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .