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.

java.sql.BatchUpdateException: Batch entry 11 INSERT INTO public.plan(description) VALUES(NULL) was aborted. Call getNextException to see the cause. This is the exception. — Poulami
– Poulami, Commented Dec 7, 2015 at 7:16
column "description" is of type bytea but expression is of type oid. How is it getting converted to oid. — Poulami
– Poulami, Commented Dec 7, 2015 at 7:40
Try setObject(++index, null) or setNull(++index, Types.OTHER) — user330315
– user330315, Commented Dec 7, 2015 at 7:41
Thanks man, it worked with setNull(++index, Types.OTHER). :) — Poulami
– Poulami, Commented Dec 7, 2015 at 7:46

user330315 · Accepted Answer · 2015-12-07 07:52:32Z

7

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

answered Dec 7, 2015 at 7:52

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

zb226 Over a year ago

One question remains, how portable are these solutions? For posterity: I've positively tested ps.setObject(++index, null); on PostgreSQL 10.10, Oracle 12.1, MySQL 8.0.18 and Derby 10.14.

Smichovan · Accepted Answer · 2016-06-08 07:49:17Z

1

This also worked for me:

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

answered Jun 8, 2016 at 7:49

Smichovan

314 bronze badges

Collectives™ on Stack Overflow

Unable to insert null in bytea field in postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related