From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-sql@postgresql.org
Subject: Re: Getting a list of a table's attributes that are sortable sorted by uniqueness
Date: Fri, 27 Jan 2012 02:13:52 +0000
Message-ID: <m3d3a56gf3.fsf@passepartout.tim-landscheidt.de> (raw)
References: <m3vcoyx3xg.fsf@passepartout.tim-landscheidt.de>
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")
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tim@tim-landscheidt.de
Subject: Re: Getting a list of a table's attributes that are sortable sorted by uniqueness
In-Reply-To: <m3d3a56gf3.fsf@passepartout.tim-landscheidt.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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