Convert a bytea column to OID while retaining values
bytea, casting, oid, postgresql, types
Solution
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.
Problem
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?