0

I have a table which contains xml file as binary data. The xmls contains "\r\n" characters as "\015\012" in bytea. I need to change the column type from bytea to varchar.

I run:

ALTER TABLE my_table ALTER COLUMN xml_data TYPE VARCHAR;
UPDATE my_table SET xml_data = convert_from(xml_data::bytea, 'UTF8');

And it works for linux. But on Windows it converts '\015' to "\r" (two characters). So I have something like that in the result:

<field>...</field>\r
<field>...</field>

Maybe there is an proper method to convert binary data to UTF?

slevin_by
  • 15
  • 3
  • No conversion will strip actual data from your input. `\r` is perfectly valid in UTF-8 too. – pozs Apr 05 '17 at 08:52

1 Answers1

0

You'll have to strip the carriage returns in a separate step.

If you are ok with getting rid of them wholesale, I suggest something like:

ALTER TABLE my_table
   ALTER xml_data TYPE text
      USING replace(
               convert_from(xml_data, 'UTF8'),
               E'\r',
               ''
            );

Is there a good reason for using data type varchar (or text, which is the same) rather than xml?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263