Unable to insert null in bytea field in postgres

Question

I am unable to insert a null value in my postgres database where the datatype is bytea(blob). This is my code snippet from java:

ps.setNull(++index, java.sql.Types.BLOB);

The bytea column is a nullable column. The following is the table description.

testdb=# \d+ plan 
                           Table "public.plan"
   Column    | Type  | Modifiers | Storage  | Stats target | Description 

-------------+-------+-----------+----------+--------------+-------------

description | bytea |           | extended |              | 

Has OIDs: no

I am getting the following exception java.sql.BatchUpdateException: Batch entry 11 INSERT INTO public.plan(description) VALUES(NULL) was aborted. Call getNextException to see the cause.

Answer 1

Postgres has two different "BLOB" types: bytea, which is essentially what the SQL standard defines as a BLOB. And "large objects" which is more or less a "pointer" to binary storage (it's still stored inside the DB).

The Postgres JDBC has always treated "large objects" as the equivalent to BLOB (which I have never understood) and thus ps.setNull(++index, java.sql.Types.BLOB); makes the driver think you are dealing with a "large object" (aka "oid") column.

To overcome this, use

ps.setNull(++index, Types.OTHER);

alternatively you can use:

ps.setObject(++index, null);