CODEWITHSUNDEEP
javapostgresqldatabase-metadata
Baruch Atta
Baruch Atta

Reputation: 431

Retrieving a field size from a PostgreSQL metadata in Java

This Java code works with a DB2 database, and a PostgreSQL database.

rs = stmt.executeQuery(qry);                            
rsMetaData = rs.getMetaData();                          
numCols = rsMetaData.getColumnCount();                  
say("resultSet MetaData column Count=" + numCols);

But when I ask for the size of any column, I get an impossible large number for a PostgreSQL

width = rsMetaData.getColumnDisplaySize(col);

DB2 returns accurate column size but PostgreSQL gives this:

table_catalog POSITION(1) varchar(2147483647)

The Driver is org.postgresql.Driver - postgresql-9.2-1002.jdbc4.jar

This size (2147483647) is returned for all the columns in the table. Is this a bug in the PostgreSQL or in Java ResultSetMetaData? How can I get the true width of columns in PostgreSQL?

Upvotes: 0

Views: 1185

Answers (3)

dyrl
dyrl

Reputation: 11

I have encountered the same problem, but without a table definition, I'm not sure we are in the same situation. In my situation, 2147483647 is returned when there is no length definition for that column (e.g. varchar, which is defined as an extension of the SQL standard).

The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively. If specified, the length must be greater than zero and cannot exceed 10485760. character without length specifier is equivalent to character(1). If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

Upvotes: 1

Baruch Atta
Baruch Atta

Reputation: 431

Here is how I handled this situation. First I read thru the entire table and get the max width of each column. Then I reread the table sequentially and display each field with it's max width. rightPad() pads spaces to make the string the right size. All the fields now line up nicely, as they should. Too bad that the PostgreSQL does not do this, or just throws up the max varchar in every case. 

while(rs.next()) {                             
    line=""; cnt++;                                          
    for (int col=1; col<=numCols; col++){                    
        fld = rs.getString(col);                             
        if (fld != null && !fld.isEmpty() &&                 
            widÝcol¨<fld.length()){widÝcol¨=fld.length();}   
    } 
> 
> rs = stmt.executeQuery(qry); // restart the query                           
cnt=0;                                                  
while(rs.next() & cnt < 10000) {                        
      line=""; cnt++;                                   
      for (int col=1; col<=numCols; col++){             
          fld = rightPad(rs.getString(col), widÝcol¨);  
          line = line + fld + " ";                      
      }                                                 
say(line + "\n");

Upvotes: 0

user330315
user330315

Reputation:

Use ResultSet.getPrecision()

Quote from the JavaDocs

Get the designated column's specified column size. For numeric data, this is the maximum precision. For character data, this is the length in characters.

(emphasis mine)

However in my experience not all JDBC driver behave the same way with this. For some getColumnDisplaySize() is more accurate, for others getPrecision() returns the correct information.

The JavaDocs for getColumnDisplaySize() state:

Indicates the designated column's normal maximum width in characters.

Which suggests this is more a hint from the driver on how wide the column can be.

Upvotes: 1

Related Questions