Why are clobs always "0"

On 12/1/19 9:31 AM, Arnie Morein wrote:

I have tested the most recent driver in three different SQL IDEs, and
now with an application I'm writing that uses JDBC metadata, the comment
on a field definition also isn't available as a string value.

The only thing I ever see regarding data type "text" field values are
either a 0 or a 1; neither of which applies.

So why is this happening, even from the JDBC metadata results as well?

You are going to have to be more specific:

1) You refer to clob, but the clob datatype is not supported:
https://www.postgresql.org/docs/11/unsupported-features-sql-standard.html
So what are you referring to?

2) Comment on field definition is what exactly?

3) How are you fetching the metadata?

--
Adrian Klaver
adrian.klaver@aklaver.com

På søndag 01. desember 2019 kl. 18:31:35, skrev Arnie Morein <
arnie.morein@mac.com <mailto:arnie.morein@mac.com>>:

I have tested the most recent driver in three different SQL IDEs, and now with
an application I'm writing that uses JDBC metadata, the comment on a field
definition also isn't available as a string value.

The only thing I ever see regarding data type "text" field values are either a
0 or a 1; neither of which applies.

So why is this happening, even from the JDBC metadata results as well?

Have you tried the NG-driver: https://github.com/impossibl/pgjdbc-ng
<https://github.com/impossibl/pgjdbc-ng&gt;
We use it with Blobs/Clobs and it's working good.

It would help us help you if you mention which IDEs you have tried, and
provide configuration-paramteres, error-messages etc.

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

Arnie Morein schrieb am 01.12.2019 um 18:31:

I have tested the most recent driver in three different SQL IDEs, and
now with an application I'm writing that uses JDBC metadata, the
comment on a field definition also isn't available as a string
value.

The only thing I ever see regarding data type "text" field values are
either a 0 or a 1; neither of which applies.

So why is this happening, even from the JDBC metadata results as
well?

The Postgres JDBC driver does not have any problems with the "text"
data type, neither with reporting it properly through DatabaseMetaData
(it's reported as Types.VARCHAR) nor with retrieving values from
such a column.

The column size for such a column is reported as 2147483647

Column comments are reliably returned in the column "REMARKS" in the
result of DatabaseMetaData.getColumns()

Given the following table:

create table test (data text);
comment on column test.data is 'The text column';

then the following Java code:

ResultSet rs = connection.getDatabaseMetaData().getColumn(null, "public", "test", "%");
rs.next();
System.out.println("column_name: " + rs.getString("COLUMN_NAME"));
System.out.println("column_size: " + rs.getInt("COLUMN_SIZE"));
System.out.println("data_type: " + rs.getInt("DATA_TYPE"));
System.out.println("remarks: " + rs.getString("REMARKS"));

will output:

column_name: data
column_size: 2147483647
data_type: 12
remarks: The text column

With 12 being the value of java.sql.Types.VARCHAR

Thomas