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 <durumd...@gmail.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 >
