pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Getting a list of a table's attributes that are sortable sorted by uniqueness 2+ messages / 1 participants [nested] [flat]
* Getting a list of a table's attributes that are sortable sorted by uniqueness @ 2011-12-30 01:07 Tim Landscheidt <tim@tim-landscheidt.de> 2012-01-27 02:13 ` Re: Getting a list of a table's attributes that are sortable sorted by uniqueness Tim Landscheidt <tim@tim-landscheidt.de> 0 siblings, 1 reply; 2+ messages in thread From: Tim Landscheidt @ 2011-12-30 01:07 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Getting a list of a table's attributes that are sortable sorted by uniqueness 2011-12-30 01:07 Getting a list of a table's attributes that are sortable sorted by uniqueness Tim Landscheidt <tim@tim-landscheidt.de> @ 2012-01-27 02:13 ` Tim Landscheidt <tim@tim-landscheidt.de> 0 siblings, 0 replies; 2+ messages in thread From: Tim Landscheidt @ 2012-01-27 02:13 UTC (permalink / raw) To: pgsql-sql 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") ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-01-27 02:13 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-12-30 01:07 Getting a list of a table's attributes that are sortable sorted by uniqueness Tim Landscheidt <tim@tim-landscheidt.de> 2012-01-27 02:13 ` Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox