> A pseudo code demonstrate it: > > select * from pg_index_columns where index_name = 'x2' > > Ordinal ColName IsAsc > 1 a False > 2 b True > > Have PGSQL same information? > > AFAIK, you can pull that information from 'indexdef' column of pg_indexes.
select * from pg_indexes where tablename='a'; Best Regards, Raghavendra EnterpriseDB Corporation > Thanks: > dd > > > > > 2011.04.01. 18:01 keltezéssel, Raghavendra írta: > > 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 >> > > >
