Subject: [pgjdbc/pgjdbc] issue #3465: Metadata CHAR_OCTET_LENGTH is a positive number for non-character column types

From: "SophiahHo (@SophiahHo)" <[email-masked]>
Date: Thu, 19 Dec 2024 17:09:56 +0000

**Describe the issue**
The method getColumns in pgjdbc's implementation of DatabaseMetaData returns `10` for CHAR_OCTET_LENGTH for column type `integer[]`. The JDBC spec [getColumns](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String-) says:

> CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column

For non-character types, the returned value should be null.

**Driver Version?** 
42.7.4

**Java Version?**
17

**PostgreSQL Version?**
13.13

**To Reproduce**

Create a table with an `integer[]` column type in your postgres DB's "public" schema:
```sql
create table myintegerarray (myintarray integer[]);
```
Java:
```java
String tablePattern = "myintegerarray";
ResultSet rs = connection.getMetaData().getColumns(null, "public", tablePattern, null);
rs.next();
System.out.println(rs.getString(16)); // CHAR_OCTET_LENGTH
```
Output:
```
10
```

**Expected behaviour**

Output:
```
null
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 19 Dec 2024 19:31:01 +0000

At first glance I would agree with you, however looking at the docs you linked to they explicitly say where null is allowed and for CHAR_OCTET_LENGTH they don't say anything. So it appears to be up to the driver how to deal with it. I can ask the spec team what they think.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 19 Dec 2024 20:43:39 +0000

I have some clarity

`CHAR_OCTET_LENGTH int => The maximum length of binary and character based columns. For any other datatype the returned value is a NULL`

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 20 Dec 2024 10:51:31 +0000

So yes, I'd be OK with a PR that fixes this

---

From: "tirthgajera (@tirthgajera)" <[email-masked]>
Date: Sat, 05 Apr 2025 11:03:28 +0000

Hi @davecramer,
I would like to take this fix.
Can you assign this issue to me?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 05 Apr 2025 16:53:20 +0000

> Hi [@davecramer](https://github.com/davecramer), I would like to take this fix. Can you assign this issue to me?

done
Thanks!

---

From: "tirthgajera (@tirthgajera)" <[email-masked]>
Date: Sat, 05 Apr 2025 19:51:05 +0000

Hi @davecramer, I raise the pull request with this fix.
Please review it once and let me know if i need to take any other actions.
https://github.com/pgjdbc/pgjdbc/pull/3589

---

From: "tirthgajera (@tirthgajera)" <[email-masked]>
Date: Sun, 06 Apr 2025 10:11:44 +0000

Hi @davecramer,
For binary value do we really need to return maximum length of column, or null should be fine?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 06 Apr 2025 10:46:48 +0000

As stated above `CHAR_OCTET_LENGTH int => The maximum length of binary and character based columns. For any other datatype the returned value is a NULL`

So for binary it is the maximum length.