Page 1 of 1

Loading data ending with \

Posted: Tue Sep 22, 2015 7:28 pm
by Beg1nner
I have a tab delimited file and one of columns has data ending with \, which I believe is cause the data to be rejected.
1234 lodestone&text=\LOHDstohn\ Agency

How should I overcome this?

Re: Loading data ending with \

Posted: Wed Sep 23, 2015 4:51 pm
by JimKnicely
Use the ESCAPE keyword to change the escape character to something else :)

Example:

Code: Select all

dbadmin=> \! cat /home/dbadmin/test.txt
destone&text=\LOHDstohn\        Agency

dbadmin=> \d test;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | test  | col1   | varchar(100) |  100 |         | f        | f           |
 public | test  | col2   | varchar(100) |  100 |         | f        | f           |
(2 rows)

dbadmin=> copy test from '/home/dbadmin/test.txt' delimiter E'\t' escape E'\001';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from test;
           col1           |  col2
--------------------------+--------
 destone&text=\LOHDstohn\ | Agency
(1 row)