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
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.