Importing bytea data into PostgreSQL by using COPY FROM stdin

Question

I generated a (UTF-8) file by an external program for importing into PostgreSQL 9.6.1. Problem is the bytea field (PWHASH).

Snippet from this file (using TAB as delimiter)

COPY USERS (ID,CODE,PWHASH,EMAIL) FROM stdin;
7   test1   E'\\\\x657B954D27B4AC56FA997D24A5FF2563'    [email protected]
\.

When importing with

psql mydb myrole -f test.sql

Everything goes well.

However, if i query the result, the byte array is not 16 bytes, but 37 bytes:

select passwordhash,length(passwordhash) from users;
                                 passwordhash                                 | length 
------------------------------------------------------------------------------+--------
 \x45275c78363537423935344432374234414335364641393937443234413546463235363327 |     37

What is the correct syntax for this?

Answer 1

The format of the input file is wrong. It should be like this:

7   test1   \\x657B954D27B4AC56FA997D24A5FF2563 [email protected]