PostgreSQL：从OID到Bytea

Question

我们决定在PostgreSQL 9.0数据库中从OID迁移到使用bytea列。我正在尝试将数据从一列复制到另一列，但我找不到正确的查询语句。这是我最接近的一个查询：

update user as thistable set pkcs_as_bytea = (select array_agg(mylargeobject.data) from 
  (select * from pg_largeobject where loid = thistable.pkcs12_as_oid order by pageno) as mylargeobject) where thistable.pkcs12 is not null

这给我带来了以下错误信息：

ERROR:  column "pkcs_as_bytea" is of type bytea but expression is of type bytea[]

那么正确的查询应该是什么？

- malaverdiere

---

array_agg() 返回一个数组，所以错误信息是有道理的。你为什么认为需要在那里聚合字节？ - user330315

我需要将位于不同oid行上的blob合并到单个列中。有更好的方法吗？ - malaverdiere

Answer 1

另一种不需要自定义函数的方法是使用 loread(lo_open(...)) 组合，例如：

UPDATE user SET pkcs_as_bytea = loread(lo_open(pkcs12_as_oid, 262144), 1000000) WHERE thistable.pkcs12 IS NOT NULL

这段代码有问题，loread函数需要第二个参数作为最大读取字节数（在上面使用的是1000000），如果你的数据很大，应该在此处使用一个非常大的数字。否则，内容将在此后被修剪，你将无法将所有数据返回到bytea字段中。

如果你想从OID转换为文本字段，你也应该使用一个转换函数，例如：

UPDATE user SET pkcs_as_text = convert_from(loread(lo_open(pkcs12_as_oid, 262144), 1000000), 'UTF8')

(262144是打开模式的标志，16进制表示为40000，意味着“只读打开”)

- Sergiu Dumitriu

---

很棒且解释得非常清楚的解决方案。 - gersonZaragocin

Answer 2

这里有一个存储过程可以实现魔法：

CREATE OR REPLACE FUNCTION merge_oid(val oid) 
returns bytea as $$
declare merged bytea;
declare arr bytea;
 BEGIN  
   FOR arr IN SELECT data from pg_largeobject WHERE loid = val ORDER BY pageno LOOP
     IF merged IS NULL THEN
       merged := arr;
     ELSE
       merged := merged || arr;
     END IF;
   END LOOP;
  RETURN merged;

END  
$$ LANGUAGE plpgsql;

- malaverdiere

Answer 3

好的，我做了类似这样的事情。我有一个带有oid类型数据的附件表和内容列。我进行了四个操作迁移：

ALTER TABLE attachment add column content_bytea bytea
UPDATE attachment SET content_bytea = lo_get(content)
ALTER TABLE attachment drop column content
ALTER TABLE attachment rename column content_bytea to content

- Ilya Khudyakov

Answer 4

This is what you can do.

--table thistable --
ALTER TABLE thistable add column se_signed_bytea bytea;
UPDATE thistable SET se_signed_bytea = lo_get(pkcs_as_bytea);
ALTER TABLE thistable drop column pkc`enter code here`s_as_bytea;
ALTER TABLE thistable rename column se_signed_bytea to pkcs_as_bytea;

- Paras Girdher

Answer 5

你需要类似 array_to_string(anyarray, text) 的函数来处理文本数组，但是在这种情况下，需要使用 array_to_bytea(largeobjectarray) 来拼接所有部分。你需要自己创建这个函数，或在应用逻辑中处理它。

- Frank Heikens