How to Insert binary data with in insert query into Postgresql using jdbc

Question

I have table structure like below

CREATE  TABLE PUBLIC.STAFF(
    STAFF_ID INT NOT NULL,
    FIRST_NAME VARCHAR(45) NOT NULL,
    LAST_NAME VARCHAR(45) NOT NULL,
    ADDRESS_ID SMALLINT NOT NULL,
    PICTURE BYTEA,
    EMAIL VARCHAR(50),
    STORE_ID INT NOT NULL,
    ACTIVE BOOLEAN NOT NULL,
    USERNAME VARCHAR(16) NOT NULL,
    PASSWORD VARCHAR(40),
    LAST_UPDATE TIMESTAMP NOT NULL
);

and I have couple of insert queries in script file or stored in arraylist like

INSERT INTO STAFF(STAFF_ID, FIRST_NAME, LAST_NAME, ADDRESS_ID, PICTURE, EMAIL, STORE_ID, ACTIVE, USERNAME, PASSWORD, LAST_UPDATE)
VALUES (
    1,
    'Mike',
    'Hillyer',
    3,
    X'89504e470d0a1a0a0000000d4948445200000079000000750802000000e55ad965000000097048597300000ec300000ec301c76fa8640000200049444154789c4cbb7794246779ffbbf78f7b7ebe466177677772ce3d9d667aa67ba62776ce39545557ce3974ee9eb049ab95563922104142580830d10203061bb049064cb031d916c160100284505aedee4cdd3f16b8b7ce73de53f5d61f75cee7bcf53ccff7fb561dbb7ce9ad972fbdf5aecb6fbd74e7a3b75f7ef4ce7bde72e9ae375fbaffcd676ebff7e29d658c864812c0e90acec0040d123aad8a284f950906205810672b140d900226b218c713028f0a5c8',
    '[email protected]',
    1,
    TRUE,
    'Mike',
    '8cb2237d0679ca88db6464eac60da96345513964',
    TIMESTAMP '2006-02-15 04:57:16.0'
);

When I am trying to insert the data into postgres using JDBC program I am getting the following error:

ERROR:  column "picture" is of type bytea but expression is of type bit
LINE 2: (1, 'Mike', 'Hillyer', 3, X'89504e470d0a1a0a0000000d49484452..                                 
HINT:  You will need to rewrite or cast the expression.
********** Error **********

ERROR: column "picture" is of type bytea but expression is of type bit

How to solve this issue using Java?

Have you thought on using a blob instead of a bytea? BTW, what's the type of your Java attribute picture? — António Ribeiro
– António Ribeiro, Commented Feb 21, 2016 at 18:19
You should be using a PreparedStatement in the first place. Then you can use setBytes(), without the need to escape the binary data. — user330315
– user330315, Commented Feb 22, 2016 at 8:47

Seva · Accepted Answer · 2022-03-05 05:12:06Z

Yes, when accessing form java it is recommended to use prepared statement. An example can be found here.

But sometimes you need to do it in SQL. In this case should use slightly different notation. Just use \x at the beginning of a string like follows (no need for any special prefixes, but note that x is lowercase):

'\x89504e470d0a1a0a0000000d4948445200000079000000750802000000e55ad965000000097048597300000ec300000ec301c76fa8640000200049444154789c4cbb7794246779ffbbf78f7b7ebe466177677772ce3d9d667aa67ba62776ce39545557ce3974ee9eb049ab95563922104142580830d10203061bb049064cb031d916c160100284505aedee4cdd3f16b8b7ce73de53f5d61f75cee7bcf53ccff7fb561dbb7ce9ad972fbdf5aecb6fbd74e7a3b75f7ef4ce7bde72e9ae375fbaffcd676ebff7e29d658c864812c0e90acec0040d123aad8a284f950906205810672b140d900226b218c713028f0a5c8'

Note that older versions of PostgreSQL only supported Escape Format which uses octal form to encode arbitrary bytes, not hex. Also note that Escape Format needs ::bytea ending.

Details can be found here (substitute version in the url to whichever you are using).

P.S. Obviously way too late for the original question, but this is where an online search lands. So, documenting it here.

Nick Burns · Accepted Answer · 2016-02-21 18:50:59Z

1

Try this:

E'\\x89504e470d0a1a0a0000000d4948445200000079000000750802000000e55ad965000000097048597300000ec300000ec301c76fa8640000200049444154789c4cbb7794246779ffbbf78f7b7ebe466177677772ce3d9d667aa67ba62776ce39545557ce3974ee9eb049ab95563922104142580830d10203061bb049064cb031d916c160100284505aedee4cdd3f16b8b7ce73de53f5d61f75cee7bcf53ccff7fb561dbb7ce9ad972fbdf5aecb6fbd74e7a3b75f7ef4ce7bde72e9ae375fbaffcd676ebff7e29d658c864812c0e90acec0040d123aad8a284f950906205810672b140d900226b218c713028f0a5c8'

answered Feb 21, 2016 at 18:50

Nick Burns

1924 bronze badges

1 Comment

pbhd Over a year ago

This is working!

Collectives™ on Stack Overflow

How to Insert binary data with in insert query into Postgresql using jdbc

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related