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)