Order PostgreSQL Table by Primary Key Automatically

postgresqlprimary-key

I want to dump the whole content of a table, ordered by its primary key. Intuitively, I would like to do a

SELECT * FROM %table_name% ORDER BY %primary_key_of(table_name)%;

This would be run by an external tool, knowing the list of the tables, but not knowing their primary keys.

If there is no better option, we can assume that all primary keys are single-column.

I am using PostgreSQL-9.5, but an upgrade is possible.

Best Answer

I believe that if you put a clustered index on your primary key, then PG will use that by default. Untested hypothesis :)

CLUSTER table_name USING primary_key_index;

According to https://www.postgresql.org/docs/current/static/sql-cluster.html

In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using CLUSTER. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, CLUSTER will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.

CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information.

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Best Answer

Related Solutions

Primary Key – Performance of Multi Field Primary Key vs ‘Contrived’ Key

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Related Question