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

durumdara <durumdara@xxxxxxxxx> · Sat, 02 Apr 2011 17:00:23 +0200



    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:
    
      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 <durumdara@xxxxxxxxx>
        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