Postgres copy命令将zip文件加载到数据库中

Question

I have a program that produces zip files and I save these temporarily in a directory like this:

  1.zip
  2.zip
  3.zip
  ...

I did a PHP program to load each of these files into a Postgres database but I need to do this with the command Postgres "copy" (because probably is faster)

I searched but I could not find the answer

So my table is like this:

  CREATE TABLE files (
  id int,
  file bytea,
  primary key (id));

So each file name is the "id" column and each zip file will be in "file" field

So how can i do this with a copy command?

Thank you all!

Answer 1

COPY ... WITH BINARY has some limitation on the supported binary format and I'm not sure it works with zip files. If you're open to INSERT, I would suggest using pg_read_file, like:

INSERT INTO files VALUES
(1, pg_read_file('/path/to/1.zip')::bytea),
(2, pg_read_file('/path/to/2.zip')::bytea),
...
;