Thread: Getting a list of a table's attributes that are sortable sorted by uniqueness
Getting a list of a table's attributes that are sortable sorted by uniqueness
From
Tim Landscheidt
Date:
Hi, 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? TIA, Tim
Re: Getting a list of a table's attributes that are sortable sorted by uniqueness
From
Tim Landscheidt
Date:
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")