RE: plpgsql copy import csv double quotes

Thanks,

As it's write in the page,<https://www.postgresql.org/docs/9.3/sql-copy.html> the file format is more a convention than a standard.

With collegues, we've find the solution with a regex in sed

sed -e 's/\([^,]\)"\([^,]\)/\1\2/g' -e 's/\([^,]\)"\([^,]\)/\1\2/g'

Because file contains values like

,"hostname1.fqdn.ad|\"\"\"\"\"\"\"\"\"\"0Clean|OK"

But, other csv file, that contains this value ","NAME=\"UBUNTU\"","| works well. I find this value in destination column |NAME=\UBUNTU\| in the destination table.

The main lines in the plpgsql function are :

CREATE OR REPLACE FUNCTION insert_into_db_sources_csv(
filename character varying,
tablename character varying,
delimiter character varying,
header character varying)
RETURNS void AS
....
request := 'TRUNCATE ' || tablename || '; COPY ' || tablename || ' FROM ''' || filename || ''' CSV ' || header || ' DELIMITER ''' || delimiter || ''' ENCODING ''UTF-8'';';
EXECUTE request;

The function call :

select insert_into_db_sources_csv('/DATA/input/files/Extract_software.csv', 't_m03_software', ',', 'HEADER');

If the import fails, TRUNCATE isn't executed. The previous data's that was in table remains the same.

_________________________________

Cordialement, Pascal CROZET

DBA

• www.qualis-consulting.com<http://www.qualis-consulting.com/> • 04 78 22 74 90

• Le Bois des Côtes 1 – Bâtiment A
• 300 Route Nationale 6 – 69760 LIMONEST
_________________________________

________________________________
De : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Envoyé : jeudi 10 octobre 2019 00:31
À : PASCAL CROZET
Cc : PG-General Mailing List
Objet : Re: plpgsql copy import csv double quotes

PASCAL CROZET <pascal(dot)crozet(at)qualis-consulting(dot)com> writes:
> I’ve experience issues with double quotes \34 inside fields, in a csv file.

> Ex :
> "value1","some text","other text with "double quotes" inside","last field"

I don't know of any definition of CSV format by which that's legal data.
The typical rule is that double quotes that are data must be doubled;
at least, that's what COPY expects by default. You can also get COPY
to handle variants like backslash-quote.

regards, tom lane