Retrieving a field size from a PostgreSQL metadata in Java

Question

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?

Which driver are you using? You could check the source to see if there's any comments about the implementation (such as "not implemented"). — Kayaman, Mar 08 '18 at 17:40
Specify exactly what JDBC driver, what version of that driver, and what version of Postgres. — Basil Bourque, Mar 08 '18 at 17:44
Googling - I find a new class: org.postgresql.jdbc Class PgDatabaseMetaData java.lang.Object org.postgresql.jdbc.PgDatabaseMetaData Could this be a clue? — Baruch Atta, Mar 08 '18 at 18:15
@horse It is the latest that is available to this large government installation. — Baruch Atta, Mar 08 '18 at 20:20

score 1 · Answer 1 · answered Mar 08 '18 at 18:31

1

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.

answered Mar 08 '18 at 18:31

Thanks for the info. But both getPrecision and getColumnDisplaySize perform exactly the same. : precision:2147483647 precision:2147483647 precision:2147483647 – Baruch Atta Mar 08 '18 at 19:09
@BaruchAtta: then the column is defined as `text` or `varchar` (without limit) in that case the value 2147483647 is correct as that is the maximum length of such a column – Mar 08 '18 at 19:10

score 1 · Answer 2 · answered Nov 18 '22 at 08:41

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.

score 0 · Answer 3 · answered Mar 08 '18 at 20:17

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");

Retrieving a field size from a PostgreSQL metadata in Java

3 Answers3