主页 / dba / 问题 / 1742
Accepted
SabreWolfy
Asked: 2011-03-15 12:38:14 +0800 CST

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

  • 772

这个问题与 bytea v. oid v. blobs v. large objects 等无关。

我有一个包含主键integer字段和bytea字段的表。我想在bytea字段中输入数据。这大概可以通过其中一种PL/语言来完成，我将来可能会考虑这样做PL/Python。

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

编辑： 2008 年的 这个讨论意味着我想做的事情是不可能的。bytea那么字段 是如何使用的呢？

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

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

postgresql plpgsql

6 个回答

  1. 使用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);

    手动的

    • 33
  2. Best Answer

    作为超级用户：

    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');
    • 31

  3. 此解决方案在运行时方面并不十分有效，但与为COPY BINARY. 此外，它不需要 bash 之外的任何库或脚本语言。

    首先，将文件转换为 hexdump，使文件大小翻倍。xxd -p让我们非常接近，但它会引发一些我们必须处理的烦人的换行符：

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

    接下来，将 PostgreSQL 中的数据作为一个非常大的text字段导入。这种类型每个字段值最多可容纳 1 GB，因此对于大多数用途我们应该没问题：

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

    现在我们的数据是一个非常大的十六进制字符串，我们使用 PostgresQLdecode将它变成一个bytea类型：

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

  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

  5. 这是在没有超级用户权限的情况下（例如在 Heroku 上）的方法。

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

    您可以使用\lo_list查看大对象并\lo_unlink删除它们。我的示例中的contract字段是bytea.

         Column      |            Type             |
contract         | bytea                       |
    • 0

  6. 使用 Postgres复制二进制函数。这大致相当于 Oracle 的外部表。

    • -1

相关问题