Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
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
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 67
Message-ID: <m3d3a56gf3.fsf@passepartout.tim-landscheidt.de>
References: <m3vcoyx3xg.fsf@passepartout.tim-landscheidt.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:GkO0AdT6wZRgXD5vWdNPM6s+0bI=

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")