The Vertica Database Forums

Posted: **Wed Feb 22, 2017 5:56 pm**

Hi,

I am getting error "Duplicate key values violates constraint 'public.table_name.C_UNIQUE' " while uploading the data to vertica table.
I need to use the ERROR TOLERANCE parameter in sql statement so that it treats each source during execution independently when loading dataThe invalid source is skipped and the load continues

I am using below query to upload the data to table,could you please suggest the correct syntax to use ERROR TOLERANCE parameter?

Code: Select all

vsql  -c "copy table_name (inputfile as 'file_name', hostname,groups, owner, job_name, job_number, unix_timestamp FILLER VARCHAR(15),submission_time AS TO_TIMESTAMPTZ(unix_timestamp) )  from local '/work/file_name' DELIMITER '|' NULL ''DIRECT REJECTED DATA '/tmp/test' TRAILING NULLCOLS;"

Posted: **Thu Feb 23, 2017 3:41 pm**

Hi,

The ERROR TOLERANCE option of the COPY command is used when your are loading from multiple sources, i.e. more than one file.

It looks like you are loading from a single file. There is a REJECTMAX parameter which specifies a maximum number of logical records that can be rejected before a load fails. However, constraint errors (i.e. UK violations) are not considered rejects.

If you really want to load the data you will have to fix the data (remove dups) or disable the UK:

Code: Select all

ALTER TABLE public.table_name.C_UNIQUE ALTER CONSTRAINT C_UNIQUE DISABLED;

Once the data is loaded, there is a function that will help you find the duplicates:

Code: Select all

select analyze_constraints('public.table_name');

The Vertica Database Forums

How to use ERROR TOLERANCE parameter in Vertica SQL?

How to use ERROR TOLERANCE parameter in Vertica SQL?

Re: How to use ERROR TOLERANCE parameter in Vertica SQL?