How to change column type from oid to bytea in PostgreSQL

user_4792543 0
Viewed 4566

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
  • System: Windows 10
  • PostgreSQL version :10
  • pgAdmin 4 version: 3.0
  • Python version 2.7.11
  • Flask version: 0.12.2

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

postgresql
1 Answers

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.

user_37330700
Related