I'm new to this very interesting blog. This is my problem: I have to load a csv file with three columns (field1, field2 and field3), in a postgresql table. In the string contained in the field1 column there are new line characters. I use sql statements:
COPY test (regexp_replace (field1,, E '[\\n\\r] +', '', 'g'),
field2, field3)
from 'D:\zzz\aaa20.csv' WITH DELIMITER '|';
but it reports me an error.
How can I remove new line characters?
If the newlines are properly escaped by quoting the value, this should not be a problem.
If your data are corrupted CSV files with unescaped newlines, you will have to do some pre-processing. If you are willing to give the database user permission to execute programs on the database server, you could use
COPY mytable FROM PROGRAM 'demangle D:\zzz\aaa20.csv' (FORMAT 'csv');
Here, demangle is a program or script that reads the file, fixes the data and outputs them to standard output. Since you are on Windows, you probably don't have access to tools like sed and awk that can be used for such purposes, and you may have to write your own.
So, this is syntax of COPY command:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
You can only add optional list of column names, and not function calls (regexp_replace in your case) or some other complex constructions.
You can create some temporal table import data into it and than copy data in your table using ordinal INSERT...SELECT query.
