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?

Laurenz Albe · Accepted Answer · 2017-02-08 16:17:27Z

9

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

7   test1   \\x657B954D27B4AC56FA997D24A5FF2563 [email protected]

answered Feb 8, 2017 at 16:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

rmuller Over a year ago

Thanks! However, where is this syntax mentioned in the documentation? I really searched some time for it :(

Laurenz Albe Over a year ago

I found it by creating a file with COPY ... TO.

rmuller Over a year ago

Yes. That is the way to do it! Thnaks again.

Vao Tsun · Accepted Answer · 2017-02-08 16:01:22Z

0

I will have to "prepare" data I believe. Smth like here:

t=# insert into u select 'x657B954D27B4AC56FA997D24A5FF2563';
INSERT 0 1
Time: 5990.809 ms
t=# select b from u;
                                  b
----------------------------------------------------------------------
 \x783635374239353444323742344143353646413939374432344135464632353633
(1 row)

Time: 0.234 ms
t=# insert into u select  decode('657B954D27B4AC56FA997D24A5FF2563','hex');
INSERT 0 1
Time: 62.767 ms
t=# select b from u;
                                  b
----------------------------------------------------------------------
 \x783635374239353444323742344143353646413939374432344135464632353633
 \x657b954d27b4ac56fa997d24a5ff2563
(2 rows)

Time: 0.208 ms

So in your case you can:

create table t as select ID,CODE,PWHASH::text,EMAIL from users where false;
COPY t (ID,CODE,PWHASH,EMAIL) FROM stdin;
insert into users select ID,CODE,decode(substr(PWHASH,4),'hex'),EMAIL from t;

edited Feb 8, 2017 at 16:01

answered Feb 8, 2017 at 15:49

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

1 Comment

rmuller Over a year ago

Yes, I know. But this cannot be used in my use case, is not it?

Collectives™ on Stack Overflow

Importing bytea data into PostgreSQL by using COPY FROM stdin

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related