CODEWITHSUNDEEP
postgresqljdbcblob
Poulami
Poulami

Reputation: 1087

Unable to insert null in bytea field in postgres

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.

Upvotes: 8

Views: 5147

Answers (2)

Smichovan
Smichovan

Reputation: 31

This also worked for me:

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

Upvotes: 1

user330315
user330315

Reputation:

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);

Upvotes: 7

Related Questions