From: | Durumdara <durumdara(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to get index columns/dir/ord informations? |
Date: | 2011-04-01 15:24:45 |
Message-ID: | AANLkTi=0FvvyM_rmDjm5OQc86=WddUPfHtw9LAnZTQq9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | pasman pasmański | 2011-04-01 15:32:33 | Convert xmin to format used by txid_current |
Previous Message | Alexander Pyhalov | 2011-04-01 11:28:36 | Re: FreeBSD/Postgres/Apache+PHP |