How to convert hex characters when using Postgres COPY FROM?

Question

I am importing data from a file to PostgreSQL database table using COPY FROM. Some of the strings in my file contain hex characters (mostly \x0d and \x0a) and I'd like them to be converted into regular text using COPY.
My problem is that they are treated as regular text and remain in the string unchanged.
How can I get the hex values converted?

Here is a simplified example of my situation:

-- The table I am importing to
CREATE TABLE my_pg_table (
    id serial NOT NULL,
    value text
);

COPY my_pg_table(id, data)
FROM 'location/data.file'
WITH CSV
DELIMITER '    ' -- this is actually a tab
QUOTE ''''
ENCODING 'UTF-8'

Example file:

1    'some data'  
2    'some more data \x0d'  
3    'even more data \x0d\x0a'

Note: the file is tab delimited.

Now, doing:

SELECT * FROM my_pg_table

would get me results containing hex.

Additional info for context:

My task is to export data from sybase tables (many hundreds) and import to Postgres. I am using UNLOAD to export data to files like so:

UNLOAD
TABLE my_sybase_table
TO 'location/data.file'
DELIMITED BY '    ' -- this is actually a tab
BYTE ORDER MARK OFF
ENCODING 'UTF-8'

Harry · Accepted Answer · 2016-02-03 21:08:48Z

1

It seems to me that (for a reason I don't understand) hex is only converted when using FORMAT TEXT and FORMAT CSV will treat it as regular string.

Solving the problem in my situation:
Because I had to use TEXT I didn't have the QUOTE option anymore and because of that I couldn't have quoted strings in my files anymore. So I needed my files in a little different format and eventually used this to export my table from sybase:

UNLOAD
    SELECT
    COALESCE(cast(id as long varchar), '(NULL)'),
    COALESCE(cast(data as long varchar), '(NULL)')
    FROM my_sybase_table
TO 'location/data.file'
DELIMITED BY '    ' -- still tab delimited
BYTE ORDER MARK OFF
QUOTES OFF
ENCODING 'UTF-8'

and to import it to postgres:

COPY my_pg_table(id, data)
FROM 'location/data.file'
DELIMITER '    ' -- tab delimited
NULL '(NULL)'
ENCODING 'UTF-8'

I used (NULL), because I needed a way to differentiate between an empty string and null. I casted every column to long varchar, to make my mass export/import more convenient.

I'd be still very interested to know why hex wouldn't convert when using FORMAT CSV.

answered Feb 3, 2016 at 21:08

Harry

1815 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

azalea Over a year ago

The answer to the question in your last paragraph: postgresql.org/docs/current/static/sql-copy.html section File Formats: "backslash is not a special character in the CSV format"

Collectives™ on Stack Overflow

How to convert hex characters when using Postgres COPY FROM?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related