By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

how to get a list of indexes created on a column of a table in postgresql?

P: 3
Hi All,

I want to know how to get a list of all indexes created on a column of a table in PostgreSQL. There is a program block in our application which do this in oracle, now i want to write the same piece of code which works for PostgreSQL as well. So, i want the list of indexes on a column of a table if i know the column name at that time or list of all the indexes on all the columns of the given table.

Below is the java Code written for Oracle DB, which now i need to rewrite for PostgreSQL. Can any body help me in how retrieve list of indexes as below?
Expand|Select|Wrap|Line Numbers
  1.  /**
  2.      *  Gets a list of all indexes on a given table and column in the mart
  3.      */
  4.     public Vector getIndexList(String column_name, String table_name)
  5.     throws EpiException, EpiSQLException
  6.     {
  7.         Vector indexList = new Vector();
  8.         String sql = "SELECT i.name FROM dbo.sysindexes i WHERE i.name NOT LIKE '[_]%' " +
  9.                     " AND i.name IS NOT NULL AND i.id = " +
  10.                         " (SELECT o.id FROM dbo.sysobjects o " +
  11.                             " WHERE UPPER(o.name) = '" + table_name.toUpperCase() + "') ";
  12.  
  13.         if (column_name != null)
  14.         {
  15.             sql += " AND EXISTS (SELECT 1 FROM dbo.sysindexkeys ik, dbo.syscolumns c " +
  16.                         " WHERE i.indid = ik.indid AND ik.colid = c.colid " +
  17.                         " AND i.id = ik.id AND c.id = ik.id " +
  18.                         " AND UPPER(c.name) = '" + column_name.toUpperCase() + "') ";
  19.         }
  20.         DBConnection con = null;
  21.         DBStatement    stmt = null;
  22.         DBResultSet rs = null;
  23.  
  24.         try
  25.         {
  26.             con = getConnection(this);
  27.             stmt = con.createStatement(this);
  28.             rs = stmt.executeQuery(sql);
  29.  
  30.             while (rs.next())
  31.             {
  32.                 indexList.addElement(rs.getString(1));
  33.             }
  34.         }
  35.         finally
  36.         {
  37.             if (rs != null)
  38.                 rs.close();
  39.             if (stmt != null)
  40.                 stmt.close();
  41.             if (con != null)
  42.                 releaseConnection(con);
  43.         }
  44.         return indexList;
  45.     }
  46.  
Oct 28 '10 #1
Share this Question
Share on Google+
1 Reply


P: 3
I myself found the answer somewhere, posting it here for reference. I am surprise for not answering this question.

String sql = "select i.relname 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 = '" + table_name.toLowerCase() + "'";

if (column_name != null)
{
sql += " AND a.attname = '" + column_name.toLowerCase() + "'";
}
Oct 30 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.