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

[venkat chitta]

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?

 /**
     *  Gets a list of all indexes on a given table and column in the mart
     */
    public Vector getIndexList(String column_name, String table_name)
    throws EpiException, EpiSQLException
    {
        Vector indexList = new Vector();
        String sql = "SELECT i.name FROM dbo.sysindexes i WHERE i.name NOT LIKE '[_]%' " +
                    " AND i.name IS NOT NULL AND i.id = " +
                        " (SELECT o.id FROM dbo.sysobjects o " +
                            " WHERE UPPER(o.name) = '" + table_name.toUpperCase() + "') ";
 
        if (column_name != null)
        {
            sql += " AND EXISTS (SELECT 1 FROM dbo.sysindexkeys ik, dbo.syscolumns c " +
                        " WHERE i.indid = ik.indid AND ik.colid = c.colid " +
                        " AND i.id = ik.id AND c.id = ik.id " +
                        " AND UPPER(c.name) = '" + column_name.toUpperCase() + "') ";
        }
        DBConnection con = null;
        DBStatement    stmt = null;
        DBResultSet rs = null;
 
        try
        {
            con = getConnection(this);
            stmt = con.createStatement(this);
            rs = stmt.executeQuery(sql);
 
            while (rs.next())
            {
                indexList.addElement(rs.getString(1));
            }
        }
        finally
        {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if (con != null)
                releaseConnection(con);
        }
        return indexList;
    }
 

[venkat chitta]

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() + "'";
}