What's the meaning of the COLUMN_SIZE 131089 for NUMERIC types without precision?

[lukaseder]

Running this:

try (Statement s = connection.createStatement()) {
    try {
        s.executeUpdate("create table t (implicit_precision numeric)");
        try (ResultSet rs = connection.getMetaData().getColumns(null, "public", "t", "%")) {
            while (rs.next()) {
                System.out.println(rs.getInt(7));
            }
        }
    }
    finally {
        s.executeUpdate("drop table if exists t");
    }
}

Produces a value of 131089, which can be seen here:
https://github.com/pgjdbc/pgjdbc/blame/0b27e26af62e8345c5045bbbc384db6d95bfd5f1/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java#L902

What is the meaning of this value? I would have expected this to produce 0 or NULL instead, just like:

select *
from information_schema.columns
where table_name = 't';

Is it safe to assume that this value won't change and I can rely on it to mean numeric (without explicit precision)?

[davecramer]

Asking if something will never change is probably a mistake. To be honest I will have to do some research to see why we used that number. For now I would expect you can assume it won't change.

[lukaseder]

Asking if something will never change is probably a mistake.

Yes, classic https://xyproblem.info. My real question here is: How does one reliably recognise a NUMERIC type without explicit user-defined precision, e.g. for the use-case of being able to re-create the original table definition, or to generate correct cast expressions (both without precision)

[davecramer]

Ok, so length refers to precision in Numeric. Why would you think it should be 0 or NULL ?

[lukaseder]

Ok, so length refers to precision in Numeric. Why would you think it should be 0 or NULL ?

1. Because that's what this query returns:

   select numeric_precision 
   from information_schema.columns
   where table_name = 't';

2. The pg_catalog also returns -1 for the precision, which is used as a base for deciding to display the display width, instead

3. Because I don't like magic numbers. I never specified that number as input, it's just a convention / configuration

4. Because I can't use the output to re-create a copy of the table, as PG rejects creating numeric columns with precision > 1000

It probably shouldn't be changed to return NULL light heartedly. Other tools may already depend on the status quo. Also, the COLUMN_SIZE value for other data types may also be specified as some magic max value (e.g. that of the TEXT type), rather than "unknown" / NULL, so if anything were changed, the change would need to be consistent for all data types.

[davecramer]

Ok, the real issue is

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java

Line 1650 in 7aea513

if (columnSize == 0) {

AFAICT this is wrong!

Now fixing it may be a challenge, but I've created issue #2188

Thanks for the report