postgresqlddlutils

Apache DdlUtils :: how to obtain the sequence name for auto-increment keys?


I am using Apache DdlUtils to query a PostgreSQL database for table and column meta-data (the end-aim being to automatically generate javax.persistence-annotated entity beans). However it seems to me that the DdlUtils library does not provide a way to obtain the names of sequences used in auto-increment columns. The Column class provides an isAutoIncrement method to query for auto-increment status but I couldn't find a way to get the sequence name associated with it. This is part of the DDL in PostgreSQL, eg:

orders=# \dS customer
                         Table "public.customer"
    Column     |       Type        |                    Modifiers
---------------+-------------------+--------------------------------------------------
 id            | integer           | not null default nextval('cst_id_seq'::regclass)
 name          | character varying | not null
 (...)

Should I directly query some metadata / catalogue table instead to obtain that bit of information?


Solution

  • Answering my own question ... Yeap, just like millimoose suggested, it can't be done with DdlUtils and you have to query the information_schema.columns table:

    public String getPostgreSQLSequenceForColumn(String database, String table, String column) {
        try {
            Statement stmt = connection.createStatement();
            String sql="select column_default from information_schema.columns where "+
                       "table_catalog='"+database+"' and "                           +
                       "table_schema='public' and "                                  +
                       "table_name='"+table+"' and "                                 +
                       "column_name='"+column+"'";
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            String sequenceName = rs.getString(1);
            panicIf(rs.next());
            return sequenceName;
        } catch (Exception e) {
            throw new ExceptionAdapter(e);
        }
    }