Is there a way to order a PostgreSQL table by its primary key automatically?

Question

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.

Answer 1

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.

Answer 2

By default, simple SELECT * FROM %table_name% will not order results by primary key.

But you can obtain list of columns of primary key of table:

SELECT ind_column.attname AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE tbl.relname = 'my_table'
  AND ind.indisprimary;

And then build dynamic query with this columns in ORDER BY clause.

Add INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace and WHERE condition on sch.nspname if you have multiple tables with the same name in different schemas.

You can obtain PK's for all tables in 1 query:

SELECT sch.nspname AS "schema"
  , tbl.relname AS "table"
  , array_agg(ind_column.attname) AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE sch.nspname <> 'pg_toast'
  AND ind.indisprimary
GROUP BY "schema", "table";