如何将数据（文件）插入PostgreSQL bytea列？

这个问题与bytea v。oid v。blob v。大对象等无关。

我有一个包含主键integer字段和bytea字段的表。我想在bytea字段中输入数据。据推测，这可以通过PL/的一种语言来完成，将来我可能会考虑使用PL/Python来实现。

由于我仍在测试和试验中，我只想使用“标准” SQL语句从文件（服务器上）插入数据。我知道，只有在服务器上具有写权限的管理员才能以我想要的方式插入数据。我现在不担心这一点，因为用户目前不会插入bytea数据。我已经搜索了各种StackExchange网站，PostgreSQL档案库和Internet，但是没有找到答案。

编辑：2008年的讨论暗示我想做的事情是不可能的。那么如何使用bytea字段？

编辑：2005年以来的类似问题仍未得到解答。

已解决：psycopg网站上此处提供的详细信息提供了我用Python编写的解决方案的基础。也可以使用bytea将二进制数据插入到PL/Python列中。我不知道使用“纯” SQL是否可行。

#1 楼

作为超级用户：

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get在9.4中引入，因此对于较旧的版本，您需要：

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

然后：

insert into my_table(bytea_data) select bytea_import('/my/file.name');

对于反向过程，我没有尝试过，但是如果可行，lo_export将是您所需要的

–杰克·道格拉斯（Jack Douglas）
2011年8月30日12:01

#2 楼

使用pg_read_file('location_of file')::bytea。

例如，

create table test(id int, image bytea);
insert into test values (1, pg_read_file('/home/xyz')::bytea);

手册

或更简单一些，pg_read_binary_file（'/ path / to / file'）。参见postgresql.org/docs/current/static/functions-admin.html

– Arto Bendiken
17年10月10日在22:17

#3 楼

此解决方案在运行时方面并非完全有效，但与为COPY BINARY制作自己的标头相比，它很容易。此外，它不需要bash之外的任何库或脚本语言。

首先，将文件转换为hexdump，使文件大小增加一倍。 xxd -p让我们非常接近，但是它会引起一些烦人的换行符，我们需要注意以下问题：

xxd -p /path/file.bin | tr -d '\n' > /path/file.hex

接下来，将数据作为非常大的qPostgreSQL导入PostgreSQL领域。此类型每个字段值最多可容纳一个GB，因此对于大多数用途，我们应该可以：

CREATE TABLE hexdump (hex text); COPY hexdump FROM '/path/file.hex';

现在我们的数据是一个非常大的十六进制字符串，我们使用PostgresQL的text将其转换为decode类型：

CREATE TABLE bindump AS SELECT decode(hex, 'hex') FROM hexdump;

此解决方案导致\ n字符从文件中删除。

–SabreWolfy
11年8月30日在11:23

SabreWolfy：不，不是。 tr -d'\ n'在xxd的输出上运行，该输出将输入的二进制内容编码为ASCII十六进制字符（0-9和a-f）。 xxd也会定期输出换行符，以使输出易于阅读，但在这种情况下，我们希望将其删除。原始数据中的换行符将采用十六进制格式，并且不会受到影响。

–好的一面
2011年8月30日15:12

#4 楼

xxd的答案很好，而且对于小型文件来说，速度非常快。以下是我正在使用的示例脚本。

xxd  -p /home/user/myimage.png | tr -d '\n' > /tmp/image.hex
echo "
    -- CREATE TABLE hexdump (hex text);
    DELETE FROM hexdump;
    COPY hexdump FROM '/tmp/image.hex';

    -- CREATE TABLE bindump (binarydump bytea);
    DELETE FROM bindump;

    INSERT INTO bindump (binarydump)  
    (SELECT decode(hex, 'hex') FROM hexdump limit 1);

    UPDATE users 
    SET image= 
    (
        SELECT decode(hex, 'hex') 
        FROM hexdump LIMIT 1
    )  
    WHERE id=15489 ;
    " | psql mydatabase

#5 楼

以下是在没有超级用户特权的情况下（例如在Heroku上）执行此操作的方法。在我的示例中，\lo_list字段为\lo_unlink。

\lo_import '/cygdrive/c/Users/Chloe/Downloads/Contract.pdf'
update contracts set contract = lo_get(:LASTOID) where id = 77;

#6 楼

使用Postgres COPY BINARY函数。这大致相当于Oracle的外部表。

谢谢。您提供的链接表明数据必须为ASCII或PostgreSQL的二进制表格式。在页面的下方，提到二进制表格式首先是使用COPY TO命令创建的。这些方法中的任何一种都可以让我将二进制文件（PDF，文档，电子表格）插入bytea列吗？

–SabreWolfy
2011-3-14在20:56

COPY BINARY上的PostgreSQL文档（postgresql.org/docs/8.4/interactive/sql-copy.html）指出，在插入二进制数据时需要特殊的文件头。我是否需要构建此标头并将其附加到二进制数据？对于简单地存储二进制数据字符串而言，这似乎有些复杂。

–SabreWolfy
2011年3月14日21:00

嗯，既然您不确定，我现在就提到它，我只是记得该命令并认为它将执行此操作。也许PL /无论采用哪种方法都是唯一的方法。

– Gaius
2011年3月14日21:05

编程黑洞网