PostgreSQL的COPY命令，二进制文件

Question

我正在使用COPY命令将表中的一个字段复制到文件中。该字段是一个压缩的文本文件，因此我使用二进制复制。 文件已创建，唯一的问题是COPY会向文件添加一个头部和尾部(?)，这部分我不需要。是否可以更改此设置？是否有参数可以使COPY将字段精确地放置在数据库中？ 如果我手动删除不需要的头部，则可以使用zcat或gunzip提取该文件。 我的实现类似于以下代码：

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

然后我想要做的是

gunzip /tmp/tmp.gz

有什么想法吗？

- user797710

Answer 1

一种可行的方法，虽然可能不太受欢迎：

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

即，将内容打印为base64并解码。我认为实际情况是psql旨在产生可读输出，并且强制其排出原始二进制数据是有意困难的。

我想，如果你足够想要它，你可以编写一些工具（Perl / Python脚本）来连接到数据库并直接打印原始输出。

复制的“WITH BINARY”选项不仅执行简单的二进制输出，而且执行某些编码，这可能是不可靠的。

- araqnid

Answer 2

您确定把压缩后的文本以二进制形式存储在数据库中是最佳方式吗？根据文档，长文本会被系统自动压缩:

长字符串会被系统自动压缩，因此磁盘上物理需求可能更少。非常长的值也会存储在后台表中，以便它们不会干扰对较短列值的快速访问。无论如何，可以存储的最长字符字符串约为1 GB。

- Grzegorz Szpetkowski

---

我同意这不是最好的方式 :-) 但决定采用它的不是我，我只是必须把它拿出来 :-) - user797710

Answer 3

复制命令即可完成任务。您只需要告诉它： --no-align 和 --tuples-only。

若要压缩，请在 psql 和文件之间使用 gzip。 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

- carlos.romel

Answer 4

我不知道有没有直接的方法... COPY 有一个可变长度头部的二进制格式，不太容易“修剪”。除此之外，PG 更偏向于文本，我认为没有办法强制从 BYTEA 字段的 SELECT 中输出“原始”（二进制）数据。

你可以获得文本十六进制输出，并编写一个小程序（C、Perl 或其他语言）将其从例如 \x000102414243 转换为二进制。这并不难，但也不是直接的方法（而且十六进制格式在 Postgresql 9.0 中）。

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

顺便说一句，Grzegorz的回答非常恰当。

补充：并不是非常干净，也不是万无一失的，只是如果有人觉得有用的话...

/* 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;
}

- leonbloy

Answer 5

不建议尝试解码PostgreSQL二进制格式。仅仅因为您正在使用的测试文件有效，并不意味着一切都能正常工作。例如，某些字符序列（未出现在您的测试文件中）可能会被转义。

- Seth Robertson

---

我不太明白。如果b是一个bytea字段（这是我的假设），那么这些输出的应该是十六进制字符串，而不是原始二进制内容。（我没有点踩） - leonbloy

@leonbloy：是的，数据类型未指定。实际输出格式取决于bytea_output() - 尽管默认格式都是编码格式。PostgreSQL不支持内联blob类型，只支持大型对象的引用。 - Seth Robertson

Answer 6

你可以使用具有客户端驱动程序和读取bytea类型的能力的编程语言来更轻松地完成此操作：PHP、Python、Ruby、Perl、JavaScript、Java等。只需在其中执行查询，使用可能已经存在于该语言中的gzip库，并将文件写出。

或者，你可以在数据库内部使用过程化语言并创建存储过程。你需要将所请求的文件名传递给存储过程。

- justis

---

相反地，我喜欢Araqnid的答案，他建议使用encode()和base64类型。 - justis