Convert a bytea column to OID while retaining values

postgresql types casting oid bytea
Petter picture Petter · Apr 13, 2012 · Viewed 7.9k times · Source

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?

Answer

Daniel Vérité picture Daniel Vérité · Apr 13, 2012

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.

Related questions

No function matches the given name and argument types

My function is: CREATE OR REPLACE FUNCTION FnUpdateSalegtab09 ( iacyrid Integer,iRepId Integer,iDrId Integer,ivrid Integer,imode smallint,itrno varchar,itrdate timestamp,iacid Integer,ivrno varchar,iSuppId Integer,icustname varchar,inetamt money,idisrate real,idisamt money,iRoundOff real,ijrmid integer,…

Read More
What does :: do in PostgreSQL?

I have seen :: in variety of places involving postgres code I have seen on the net. For example: SELECT '{apple,cherry apple, avocado}'::text[]; It seems to be some sort of cast. What exactly is :: in postgres and …

Read More
ERROR: failed to find conversion function from unknown to text

There is an error on PostgreSQL that it gives on one of my select statements. I searched the web for an answer and came out empty handed. The answer given in another question did not suit my problem. ERROR: failed …

Read More