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 <t...@sss.pgh.pa.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.cro...@qualis-consulting.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
