postgresql - How to insert (raw bytes from file data) using a plain text script -

database: postgres 9.1

i have table called logos defined this:

create type image_type enum ('png'); create table logos (   id uuid primary key,   bytes bytea not null,   type image_type not null,   created timestamp time zone default current_timestamp not null ); create index logo_id_idx on logos(id); 

i want able insert records table in 2 ways.

the first (and common) way rows inserted in table user provide png image file via html file upload form. code processing request on server receive byte array containing data in png image file , insert record in table using similar explained here. there plenty of example of how insert byte arrays postgresql field of type bytea on internet. easy exercise. example of insert code this:

insert logos (id, bytes, type, created) values (?, ?, ?, now())  

and bytes set like:

... byte[] bytes = ... // read png file byte array. ... ps.setbytes(2, bytes); ... 

the second way rows inserted in table plain text file script. reason needed populate test data table automated tests, or initialize database few records remote development environment.

regardless of how data entered in table, application need able select bytea data table , convert png image.

---

question

how 1 encode byte array, able insert data within script, in such way original bytes contained in file stored in database?

i can write code read file , spit out insert statements populate script. don't know how encode byte array plain text script such when running script psql image data same if file inserted using setbytes jdbc code.

i run script this:

psql -u username -d database -a -f test_data.sql 

the easiest way, imo, represent bytea data in sql file use hex format:

8.4.1. bytea hex format

the "hex" format encodes binary data 2 hexadecimal digits per byte, significant nibble first. entire string preceded sequence \x (to distinguish escape format). in contexts, initial backslash may need escaped doubling it, in same cases in backslashes have doubled in escape format; details appear below. hexadecimal digits can either upper or lower case, , whitespace permitted between digit pairs (but not within digit pair nor in starting \x sequence). hex format compatible wide range of external applications , protocols, , tends faster convert escape format, use preferred.

example:

select e'\\xdeadbeef'; 

converting array of bytes hex should trivial in language sane person (such yourself) use write sql file generator.