Storing an image as a byte[] using import.sql with spring

Question

I'm trying to add an image file into a database using an import.sql file when spring is loaded. I'm currently using postgresql.

My byte array is in my project class:

 @Lob
 private byte[] bytes;

I've attempted to import the file via pg_read_file like so:

-- Insert into Project

insert into project (title, colour, description, project_user_id, bytes) values ('Test', '#F6D2B4', 'a short description of some project, making sure its over 50 characters', 1, pg_read_file('classpath:static/images/pon9s2N.jpg'))

but I get the following error when running spring:

2018-04-29 10:33:43.812 ERROR 540 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000388: Unsuccessful: insert into project (title, colour, description, project_user_id, bytes) values ('Test', '#F6D2B4', 'a short description of some project, making sure its over 50 characters', 1, pg_read_file('classpath:static/images/pon9s2N.jpg'))

2018-04-29 10:33:43.813 ERROR 540 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: column "bytes" is of type oid but expression is of type text
Hint: You will need to rewrite or cast the expression.
Position: 179

I've tried using the CAST(pg_read... AS byte[]) but no luck.

Thanks.

Answer 1

Use pg_read_binary_file to read a binary file.