Thread: On Conflict Do nothing errors IF conflict and there is a data type length or check failure
On Conflict Do nothing errors IF conflict and there is a data type length or check failure
From
"Regina Obe"
Date:
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
Re: On Conflict Do nothing errors IF conflict and there is a data type length or check failure
From
Amit Langote
Date:
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