Thread: How to get index columns/dir/ord informations?
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
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
EnterpriseDB Corporation
On Fri, Apr 1, 2011 at 8:54 PM, Durumdara <durumdara@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 columnSo 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
Hi!
The pg_index, and pg_indexes is good for I get the index names, and types.
I have two indexes on test table "a":
CREATE INDEX ix1
ON a
USING btree
(a);
CREATE UNIQUE INDEX x2
ON a
USING btree
(a DESC, b);
From this I can recognize the type (unique or normal) of the index, but none of the columns.
I don't found any tables that can say to me, which columns with which direction used in index.
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?
Thanks:
dd
2011.04.01. 18:01 keltezéssel, Raghavendra írta:
The pg_index, and pg_indexes is good for I get the index names, and types.
I have two indexes on test table "a":
CREATE INDEX ix1
ON a
USING btree
(a);
CREATE UNIQUE INDEX x2
ON a
USING btree
(a DESC, b);
From this I can recognize the type (unique or normal) of the index, but none of the columns.
I don't found any tables that can say to me, which columns with which direction used in index.
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?
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 astable 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 CorporationOn Fri, Apr 1, 2011 at 8:54 PM, Durumdara <durumdara@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 columnSo 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
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
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 astable 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 CorporationOn Fri, Apr 1, 2011 at 8:54 PM, Durumdara <durumdara@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 columnSo 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