On 6/14/19 2:04 PM, Leandro Guimarães wrote:
Hi,
I have a scenario with a large table and I'm trying to insert it via
a COPY command with a csv file.
Everything works, but sometimes my source .csv file has duplicated
data in the previously fulfilled table. If I add a check constraint and
try to run the COPY command I have an error that stops the whole insertion.
I've tried to put the data in a tmp table and fill the main using
distinct this way (the fields and names are just examples):
INSERT INTO final_table values (name, document)
SELECT DISTINCT name, document
FROM tmp_TABLE t1
WHERE NOT EXISTS (
SELECT 1 FROM final_table t2
WHERE (t2.name <http://t2.name>, t2.document)
IS NOT DISTINCT FROM (t1.name <http://t1.name>, t1.document))
The problem is that my final_table is a large (and partitioned) table
and this query is taking a long time to execute.
Someone have any idea (really guys anything would be great) how to solve
this situation? I need to ignore duplicates instead to have some error.
pg_bulkload?:
https://ossc-db.github.io/pg_bulkload/pg_bulkload.html
"PARSE_ERRORS = n
The number of ingored tuples that throw errors during parsing,
encoding checks, encoding conversion, FILTER function, CHECK constraint
checks, NOT NULL checks, or data type conversion. Invalid input tuples
are not loaded and recorded in the PARSE BADFILE. The default is 0. If
there are equal or more parse errors than the value, already loaded data
is committed and the remaining tuples are not loaded. 0 means to allow
no errors, and -1 and INFINITE mean to ignore all errors. "
I'm using*PostgreSQL 9.4* so I can't use "ON CONFLICT" and upgrade is
not an option.
Thanks and Kind Regards!
Leandro Guimarães
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
