I’m using the PostgreSQL client application, psql ¹ almost daily. One thing I never got it to do, however, was reading binary files into fields of type bytea. Until now! Here’s how it can be done in pure psql without any custom coding.

I’m importing the 50279 bytes file elephant.png:

testdb=> create table files (id int, data bytea);
CREATE TABLE
testdb=> \lo_import 'Downloads/elephant.png';
lo_import 16409
testdb=> insert into files values (1, lo_get(16409));
INSERT 0 1
testdb=> \lo_unlink 16409
lo_unlink 16409
testdb=> select id, octet_length(data) from files;
 id | octet_length
----+--------------
  1 |        50279
(1 row)

I knew about large objects and the \lo commands. But, it never occurred to me, they could be used to easily import file data into a plain bytea field.