Chris Mair

Language selector

One more psql trick

I’m using the PostgreSQL client application, psql 1 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);
testdb=> \lo_import 'Downloads/elephant.png';
lo_import 16409
testdb=> insert into files values (1, lo_get(16409));
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.


Language selector