Insert binary file with COPY in Postgres

Question

I have a Postgres database that I am populating with CSV files using COPY. Some tables have columns of type bytea, and in these CSV files it is specified the path to the binary file. Given the table

CREATE TABLE mytable
( ID INTEGER,
  File BYTEA
);

I know I can insert binary files with

INSERT INTO mytable VALUES (1, pg_read_binary_file('/path/to/file'));

but, can I specify to do so with COPY reading the /path/to/file from the CSV files? Can you apply functions to COPY in Postgres? I know in MySQL you can do it using something like

LOAD DATA
INFILE /path/to/csv
INTO TABLE mytable
( ID,
  @File
)
SET File = LOAD_FILE(@File);

but I have to stick to Postgres and store the binary files in the database.

You need to pick a programming language and write a program replacing these paths by the contents of the files in hex. — Daniel Vérité
– Daniel Vérité, Commented Mar 15, 2019 at 13:07

Richard Huxton · Accepted Answer · 2019-03-15 13:17:32Z

1

Import your csv file into a temporary table without processing the filenames.

Then have a separate INSERT that calls pg_read_binary_file.

answered Mar 15, 2019 at 13:17

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert binary file with COPY in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related