将字节列转换为OID，同时保留值 [英] Convert a bytea column to OID while retaining values

问题描述

我试图改变一个 bytea 列，使其具有类型 oid ，但仍保留这些值。 p>

我尝试过使用如下查询：

  ALTER TABLE mytable ADD COLUMN mycol_tmp oid; 
 UPDATE mytable SET mycol_tmp = CAST（mycol as oid）; 
 ALTER TABLE mytable DROP COLUMN mycol; 
 ALTER TABLE mytable RENAME mycol_tmp To mycol; 
  

但这只是给我错误：

 错误：无法将类型bytea转换为oid 
  

解决方案

类型Oid的列只是对实际存储的二进制内容的引用在系统的 pg_largeobject 表中。在存储方面，一个Oid是一个4字节的整数。
另一方面，bytea 类型的列是

实际内容。

要将bytea转换为大对象，应该使用大对象的类文件API创建一个新的大对象：lo_create（）在写模式下获取一个新的OID，然后是lo_open（），然后使用lo_write（）或lowrite（）写入，然后lo_close

基本上，你需要写一个一个〜10行的代码以您选择的语言（至少一个支持大对象API，包括plpgsql）来做这个转换。

I'm trying to alter a bytea column to have type oid and still retain the values.

I have tried using queries like:

ALTER TABLE mytable ADD COLUMN mycol_tmp oid;
UPDATE mytable SET mycol_tmp = CAST(mycol as oid);
ALTER TABLE mytable DROP COLUMN mycol;
ALTER TABLE mytable RENAME mycol_tmp TO mycol;

But that just gives me the error:

ERROR: cannot cast type bytea to oid

Is there any way to achieve what I want?

解决方案

A column of type Oid is just a reference to the binary contents which are actually stored in the system's pg_largeobject table. In terms of storage, an Oid a 4 byte integer. On the other hand, a column of type bytea is the actual contents.

To transfer a bytea into a large object, a new large object should be created with the file-like API of large objects: lo_create() to get a new OID, then lo_open() in write mode, then writes with lo_write() or lowrite(), and then lo_close().

This can't reasonably be done with just a cast.

Basically, you would need to write a ~10 lines piece of code in the language of your choice (at least one that supports the large object API, including plpgsql) to do this conversion.

这篇关于将字节列转换为OID，同时保留值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！