From: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
---|---|
To: | Durumdara <durumdara(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to get index columns/dir/ord informations? |
Date: | 2011-04-01 16:01:13 |
Message-ID: | AANLkTi=XNHmBD6tjjY6nvWrZYEDHiNi3bFeO+1SUPdn0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Query to list the tables and its concerned indexes.
SELECT indexrelid::regclass as index , relid::regclass as
table FROM pg_stat_user_indexes JOIN pg_index USING
(indexrelid) WHERE idx_scan < 100 AND indisunique IS FALSE;
Query will list the contraints.
SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index,
pg_class WHERE pg_class.oid=pg_index.indrelid AND ( indisunique = 't' OR
indisprimary = 't' ) );
To get the column order number, use this query.
SELECT a.attname,a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE
c.relname = 'vacc' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid =
t.oid;
Note: This query for a particular Table 'VACC'
Best Regards,
Raghavendra
EnterpriseDB Corporation
On Fri, Apr 1, 2011 at 8:54 PM, Durumdara <durumdara(at)gmail(dot)com> wrote:
> Hi!
>
> I want to migrate some database to PG.
> I want to make intelligens migrator, that makes the list of the SQL-s what
> need to do to get same table structure in PG as in the Source DB.
>
> All things I can get from the views about tables, except the indices.
>
> These indices are not containing the constraints - these elements I can
> analyze.
>
> I found and SQL that get the index columns:
>
>
> select
> t.relname as table_name,
> i.relname as index_name,
> a.attname as column_name
> from
> pg_class t,
> pg_class i,
> pg_index ix,
> pg_attribute a
> where
> t.oid = ix.indrelid
> and i.oid = ix.indexrelid
> and a.attrelid = t.oid
> and a.attnum = ANY(ix.indkey)
> and t.relkind = 'r'
> and t.relname = 'a'
> and ix.indisunique = 'f'
> and ix.indisprimary = 'f'
> order by
> t.relname,
> i.relname;
>
> This can list the columns. But - what a pity - this don't containing that:
> - Is this index unique?
> - What the direction of the sort by columns
> - What is the ordinal number of the column
>
> So everything what I need to analyze that the needed index is exists or
> not.
>
>
> Please help me: how can I get these informations?
> I don't want to drop the tables everytime if possible.
>
> Thanks:
> dd
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2011-04-01 16:25:47 | Re: Adding PK to Existing Table [RESOLVED] |
Previous Message | Raghavendra | 2011-04-01 15:43:52 | Re: Adding PK to Existing Table |