Обсуждение: On Conflict Do nothing errors IF conflict and there is a data type length or check failure

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.


Thanks,
Regina

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