Here is the data file:
Code: Select all
dbadmin=> \! cat test1.txt
"a""b","c
d"
Code: Select all
dbadmin=> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
public | test | a | varchar(512) | 512 | | f | f |
public | test | b | varchar(512) | 512 | | f | f |
(2 rows)
Code: Select all
COPY test (
fa FILLER VARCHAR(512),
fb FILLER VARCHAR(512),
a AS TRIM(BOTH '"' FROM fa),
b AS TRIM(BOTH '"' FROM fb))
FROM '/usr/home/dbadmin/test1.txt'
DELIMITER ','
ESCAPE AS E'\001'
RECORD TERMINATOR E'"\n';
Code: Select all
dbadmin=> COPY test (
dbadmin(> fa FILLER VARCHAR(512),
dbadmin(> fb FILLER VARCHAR(512),
dbadmin(> a AS TRIM(BOTH '"' FROM fa),
dbadmin(> b AS TRIM(BOTH '"' FROM fb))
dbadmin-> FROM '/usr/home/dbadmin/test1.txt'
dbadmin-> DELIMITER ','
dbadmin-> ESCAPE AS E'\001'
dbadmin-> RECORD TERMINATOR E'"\n';
Rows Loaded
-------------
1
(1 row)
Code: Select all
dbadmin=> select * from test;
a | b
------+-----
a""b | c
d
(1 row)
Code: Select all
COPY test(
fa FILLER VARCHAR(512),
fb FILLER VARCHAR(512),
a AS TRIM(BOTH '"' FROM REPLACE(fa, '""', '"')),
b AS TRIM(BOTH '"' FROM REPLACE(fb, '""', '"')))
FROM '/usr/home/dbadmin/test.txt'
DELIMITER ','
ESCAPE AS E'\001'
RECORD TERMINATOR E'"\n';
Code: Select all
dbadmin=> COPY test(
dbadmin(> fa FILLER VARCHAR(512),
dbadmin(> fb FILLER VARCHAR(512),
dbadmin(> a AS TRIM(BOTH '"' FROM REPLACE(fa, '""', '"')),
dbadmin(> b AS TRIM(BOTH '"' FROM REPLACE(fb, '""', '"')))
dbadmin-> FROM '/usr/home/dbadmin/test.txt'
dbadmin-> ESCAPE AS E'\001'
dbadmin-> DELIMITER ','
dbadmin-> ESCAPE AS E'\001'
dbadmin-> RECORD TERMINATOR E'"\n';
Rows Loaded
-------------
1
(1 row)
Code: Select all
dbadmin=> select * from test;
a | b
--------+--------
a""b | c
d
a"b | c
d
(2 rows)