Re: [GENERAL] How to get index columns/dir/ord informations?

[Raghavendra]

> A pseudo code demonstrate it:
>
> select * from pg_index_columns where index_name = 'x2'
>
> Ordinal   ColNameIsAsc
> 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  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
>>
>
>
>

Re: [GENERAL] How to get index columns/dir/ord informations?

[durumdara]

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   ColNameIsAsc
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 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 > 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

Re: [GENERAL] How to get index columns/dir/ord informations?

[Raghavendra]

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  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
>

[GENERAL] How to get index columns/dir/ord informations?

[Durumdara]

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