1

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.

  • would a clustered index not do this? – Neil McGuigan Jul 10 '18 at 16:51
  • @NeilMcGuigan Is there any reason for postgresql to do give back the result by the clustered index? Also I think so would it be rational, but it would still strongly depend on the postgresql internals, isn't it? – peterh Jul 10 '18 at 18:17
  • @NeilMcGuigan Wonderful! Check this: postgresonline.com/journal/archives/… Cluster, then pg_dump! Make it an answer! – peterh Jul 10 '18 at 18:21
  • Do not rely on that! The only (really: the only) way to get a sorted result is to use order by. There is no alternative. Any order you see without an order by is pure coincidence. Plus: cluster is a one-time operation, that ordering is not maintained by Postgres. – a_horse_with_no_name Jul 10 '18 at 19:08
  • @a_horse_with_no_name What if, what I want is to actually minimize the git repos where the postgresql dumps are backed up? In this case, a little deviation from the perfect ordering is only an optimization. In this case, CLUSTER on all the tables and then a pg_dump would do nearly exactly what I want, with nearly zero extra work. – peterh Jul 10 '18 at 19:11
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.

  • 1
    As long as you do not supply an ORDER BY there is absolutely no guarantee that the result is order. clustering the table does not guarantee any result ordering without an ORDER BY. The only way to get a sorted result is to use ORDER BY, there is no alternative – a_horse_with_no_name Jul 10 '18 at 19:07
  • As it says in those docs, clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. And that aside, the query planner is free to return rows in whichever order it deems most efficient. This definitely won't work if it opts for a parallel scan. – Nick Barnes Oct 9 '18 at 15:29
4

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";
  • Also your answer is very useful! I had no idea, which deserves more the accept. Sorry! – peterh Jul 10 '18 at 19:14
  • Don't forget to ORDER BY ind_column.attnum! – Nick Barnes Oct 9 '18 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.