1

I have a text column in my database which was being used to store pickled python data. This of course became a problem because this pickled data was being decoded as UTF-8 as it was being stored and occasionally caused issues.

I would like to alter the type of the column to the binary data type bytea to properly store this data. To test all the currently stored data could be converted I ran the following query:

SELECT text_column::bytea as result FROM table;

Which ran without issues. However, attempting to ALTER the column's type using the query:

ALTER TABLE table ALTER COLUMN text_column TYPE bytea USING text_column::bytea;

Results in the error:

ERROR: invalid input syntax for type bytea

Similarly:

ALTER TABLE table ALTER COLUMN text_column TYPE bytea USING CAST(text_column AS bytea);

To test this wasn't actually a syntax error I converted to text without any issues:

ALTER TABLE table ALTER COLUMN text_column TYPE text USING CAST(text_column AS text);

At this point I'm not really sure what the issue is if I can cast without issue when SELECTing the results. The column has no default. My only thought now would be to create a temporary table containing the casted values, dropping the column, adding it back as a bytea column and bringing back the casted values. But I'd still like to know what the cause is of the original syntax error?

Silversonic
  • 1,289
  • 2
  • 11
  • 26
  • This was more or less a duplication of [this](https://stackoverflow.com/questions/19300113/unable-to-convert-postgresql-text-column-to-bytea) which has the correct answer. The conversion is interpreting the text value as escaped input, which has backslashes - it's not taking the text value at face value. The solution is to escape the backslashes by replacing `text_column::bytea` with `replace(text_column, '\', '\\')::bytea`. – Silversonic Jun 13 '18 at 22:16

0 Answers0