Re: On Conflict Do nothing errors IF conflict and there is a data type length or check failure

Hi,

On 2016/02/17 14:34, Regina Obe wrote:
> I'm guessing this is by design but just wanted to confirm that since it
> makes this feature not as useful for us.  
> 
> It also wasn't absolutely clear to me from the documentation.
> 
> We are running PostgreSQL 9.5.1 and if we do something like:
> 
> CREATE TABLE test(field1 varchar(5) primary key, field2 varchar(3));
> 
> INSERT INTO test(field1, field2) VALUES ('test','tes');
> 
> INSERT INTO test(field1,field2) VALUES('test', 'test')
> ON CONFLICT(field1) DO NOTHING;
> 
> It triggers an error:
> 
> ERROR:  value too long for type character varying(3)
> 
> I think it does this for check constraints too.
> 
> 
> Even though the record under consideration would be thrown out anyway.

I think the error occurs long before it would/could be determined that it
won't be inserted anyway (the latter being execution time). It would be
quite out-of-place to document such behaviors under ON CONFLICT or check
constraints description, IMHO.

Thanks,
Amit