CODEWITHSUNDEEP
postgresql
Spider
Spider

Reputation: 1470

How to change column type from oid to bytea in PostgreSQL

At first place, I got this error: 

org.postgresql.util.PSQLException: ERROR: column xxxx is of type oid but expression is of type bytea
Hint: You will need to rewrite or cast the expression.
Position: 318
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
...

After googled a while, I think I should change the column type from oid to bytea. I tried this in the UI pgAdmin:

ALTER TABLE theTableName
ALTER COLUMN xxxx TYPE bytea

But I got error:

ERROR:  column "xxxx" cannot be cast automatically to type bytea
HINT:  You might need to specify "USING xxxx::bytea".
SQL state: 42804

Then I tried:

ALTER TABLE theTableName
ALTER COLUMN xxxx TYPE bytea USING xxxx::bytea

And I got this error:

ERROR:  cannot cast type oid to bytea
LINE 2: ALTER COLUMN xxxx TYPE bytea USING xxxx::bytea
                                                ^
SQL state: 42846
Character: 88

May I ask how to solve this? Thank you so much!

Upvotes: 4

Views: 9963

Answers (1)

404
404

Reputation: 8542

Cast to TEXT then to BYTEA:

ALTER TABLE theTableName
ALTER COLUMN xxxx TYPE bytea USING xxxx::TEXT::BYTEA

WARNING

Note that this does not magically migrate any data! Hence, the large object sitting in pg_largobject referenced by the value stored in the oid column is not copied to the theTableName table. Only run this on empty tables or if you can afford to lose the large objects.

Upvotes: 7

Related Questions