Geek asks and answers

Copy postgres binary command

I use COPY to copy a field from a table to a file. this field is a text file with a zipper, so I use a binary copy. the file is created, the only problem is that COPY adds the header and trailer (?) to the file that I don't need. can this be changed? is there a parameter that can cause COPY to put the field exactly as it is in the database?

If I manually delete the unwanted header, I can extract the file using zcat or gunzip.

I am doing something like this:

psql -d some_database -c \
 "copy  (select some_column from a_table where id=900) to stdout with BINARY;" > /tmp/tmp.gz

And then I want to do

gunzip /tmp/tmp.gz

Any ideas?

+3
source share
6 answers

One feature that works, although you may not like it:

psql -At -c "select encode(content, 'base64') from t where ..."  | base64 -d

. base64 . , , psql , , .

, , - (Perl/python script) .

"WITH BINARY" COPY , , , , , .

+3

, zipped- ? / :

, . , . , 1 .

+1

... COPY , "". , PG , , "" () SELECT BYTEA.

(C, perl - ), say \x000102414243 . , ( Postgresql 9.0)

psql  -t -q -c "select binaryfield from.. where ..." mydb  |  myhextobin > tmp.gz

, .

: , , - ...

/* expects a pg hexadecimal string, in "\x....." format, and converts to binary*/
/* warning: no checks! it just ignores chars outside [0-9a-f] */
#include<stdio.h>
int main() {
    int x, pos, v;
    char hex[3]={0,0,0};
    pos = 0;
    while( (x = getchar()) >= 0) {
        if(( x >='0' && x <= '9') || ( x >= 'a' && x <= 'f' )) {
            hex[pos++] = (char)x;
            if(pos == 2) {
                sscanf(hex, "%x", &v);
                putchar((char)v);
                pos = 0;
            }
        }
    }
    return pos==0 ? 0 : 1;
}
+1

postgresql. , , , , . , , ( ) .

0

, bytea: PHP, python, ruby, perl, javascript, java .. , gzip , , , , .

Alternatively, you can use the procedural language inside the database and create a stored procedure. You must pass the requested file name to the stored procedure.

0
source

The copy command is completing the task. You only need to say: --no-alignand --tuples-only.

For compression use gzipbetween psql and file

psql --tuples-only --no-align -d some_database -c \ "copy (select some_column from a_table where id=900) to stdout with BINARY;" | gzip > /tmp/tmp.gz

0
source

More articles:


All Articles