I wrote:
> I frequently use pg_dump to dump databases and compare them
> with diff. To get rid of most "false positives", I'd like
> to patch pg_dump to sort the table so that its dumped order
> isn't changed more than necessary by insertions & Co.
> So I'm looking for a query that will return a list of a
> table's attributes that are sortable (e. g. no XML fields)
> and sorted by "uniqueness", i. e. first attributes repre-
> senting the primary key, then other unique keys, then the
> rest.
> Before I dive into the depths of PostgreSQL's system cata-
> logues, has anyone already solved this problem?
Progress report: The query:
| SELECT attname,
| attnum,
| keyrank,
| columnrankinkey
| FROM pg_attribute
| LEFT JOIN
| (SELECT DISTINCT ON (tableid,
| columnnr) indrelid as tableid,
| indkey [subscript] AS columnnr,
| CASE
| WHEN indisprimary THEN 0
| WHEN indisunique THEN 1
| ELSE 2
| END as keyrank,
| subscript as columnrankinkey
| FROM
| (SELECT indrelid,
| indkey,
| generate_subscripts(indkey, 1) as subscript,
| indisprimary,
| indisunique
| FROM pg_index
| ORDER BY indrelid,
| indkey,
| indisprimary DESC, indisunique DESC) AS s
| ORDER BY tableid, columnnr, CASE
| WHEN indisprimary THEN 0
| WHEN indisunique THEN 1
| ELSE 2
| END, columnrankinkey) AS s2 ON attrelid = tableid
| AND attnum = columnnr
| WHERE attrelid = 'tablename'::regclass
| AND NOT attisdropped
| AND attnum > 0
| ORDER BY keyrank,
| columnrankinkey,
| attnum;
does almost what I want except:
- Attributes that can't be sorted (XML) aren't skipped, and
- "UNIQUE(A, B)" and "UNIQUE(C, D)" would give "A, C, B, D" (untested) so the "rank" of a non-primary key has yet to be
included.
Stay tuned.
Tim
(looking forward to "UNNEST ... WITH ORDINALITY")
