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