CODEWITHSUNDEEP
postgresqlcsvimport
rmuller
rmuller

Reputation: 12859

Importing bytea data into PostgreSQL by using COPY FROM stdin

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?

Upvotes: 2

Views: 7140

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247495

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

7   test1   \\x657B954D27B4AC56FA997D24A5FF2563 [email protected]

Upvotes: 9

Vao Tsun
Vao Tsun

Reputation: 51609

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:

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

Upvotes: 0

Related Questions