Postgres Copy Command, Binary File

Parsing COPY...WITH BINARY results

The general format of a file generated by COPY...BINARY is explained in the documentation, and it's non-trivial.

bytea contents are the most easy to deal with, since they're not encoded.

Each other datatype has its own encoding rules, which are not described in the documentation but in the source code. From the doc:

To determine the appropriate binary format for the actual tuple data
you should consult the PostgreSQL source, in particular the *send and
*recv functions for each column's data type (typically these functions are found in the src/backend/utils/adt/ directory of the source
distribution).

Insert binary file with COPY in Postgres

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

Then have a separate INSERT that calls pg_read_binary_file.

using \COPY with BINARY for lo_export in postgreSQL on server system

Basically:

1. lo_export will instruct the server to write a file locally (always)
2. \copy will be transformed by psql to a COPY ... TO STDOUT command and the output written to the specified file. (So what is written to that file is the result of the select statment you were doing before)

So, you can not use lo_export in this way, it will always write a file onto the server's filesystem.

Of course, you can solve this simply by having the server write to a shared drive, and then read the file from that drive. Ugly, but effective IMHO.

For some recent versions of psql (not sure when this was introduced) there is a \lo_export psql command which takes an OID and filename, e.g.:

\lo_export 28914648 testfile

However you need to get the OID of the file into the script somehow...

You can write a PL/PGsql function like this to dump the file as a bytea:

CREATE OR REPLACE FUNCTION getfiledata(lobjid oid) RETURNS bytea
  STRICT STABLE LANGUAGE plpgsql AS $$
DECLARE
  fd int4;
  imgsize int4;
  INV_READ int4 := 262144;
  SEEK_SET int4 := 0;
  SEEK_END int4 := 2;
BEGIN
  SELECT lo_open(lobjid, INV_READ) INTO fd;
  PERFORM lo_lseek(fd, 0, SEEK_END);
  SELECT lo_tell(fd) INTO imgsize;
  PERFORM lo_lseek(fd, 0, SEEK_SET);
  RETURN loread(fd, imgsize);
END;
$$;

Now calling this function with the OID of the large object will return its content as a bytea value. You can thus call this function in a COPY command and it will return the file data... and by using \copy it will be sent to the client.

It's generally recommended these days to use bytea columns directly rather than this large object interface (bytea was introduced a lot later). PostgreSQL will automatically move large values into out-of-line storage ("TOAST tables") and will also compress them (unless the storage mode is set to "external" to suppress this, which is probably the right thing to do for JPEG images etc)

EDIT :
\lo_export
Try this from commandprompt.com

 `"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d myDB -U DB_admin -c  "\lo_export 19135 'C://leeImage.jpeg' ";`

where the number 19135 is the OID of the species whose image i want on the client system.. the OID you can get from the fishes table fishesimages OID
use the OID in the above code and you can use the OID get the images.

---

Related Topics