Re: Bug in DatabaseMetaData.getColumns() with columns based on domains

Thomas Kellerer wrote:
> Thomas Kellerer wrote on 21.10.2010 14:57:
>> Hi,
>>
>> consider the following table and domain:
>>
>> CREATE DOMAIN salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0);
>> CREATE TABLE employee (id integer not null, salary salary_domain);
>>
>> DatabaseMetaData.getColumns(null, "public", "employee", "%");
>>
>> returns "YES" for the column IS_NULLABLE in the ResultSet whereas it
>> should flag that column as not nullable.
>>
>
> OK, I realized that not even psql or pgAdmin will display such a column
> as "not null"
> So it is not a JDBC driver issue (but rather a PostgreSQL "core" issue)
>
> Sorry for the noise
> Thomas
>
>

When the table field salary is defined directly the correct attribute for
IS_NULLABLE is returned by the DatabaseMetaData.getColumns(). When the
field is defined indirectly through the DOMAIN then the database appears
to be not setting the attnotnull entry correspondingly. As you indicated
I would also say it is the database. The attached files indicate the sql
statement generated by the JDBC and the result set for the getColumns()
for the table.

The early attached file query for the column's information was from the
information_schema.columns table and does show the correct result for the
salary field IS_NULLABLE.

danap.