SET Table Duplicate Error

Database
Highlighted
New Member

SET Table Duplicate Error

Hi,
I have the following  table definition-
create set volatile table LOAD_ASC_TST (ID_NBR integer,ENTER_TS timestamp(6),OBSOLETE_TS timestamp(6)) PRIMARY INDEX(ID_NBR) ON COMMIT PRESERVE ROWS;
 
1) Insert with values (123,cast('2008-03-09 03:01:30.610610' as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'),NULL); ==> SUCCESS

2) Insert with values (123,cast('2008-03-09 02:01:30.610610' as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'),NULL); ==> FAILED

      [Error 2802] [SQLState 23000] Duplicate row error

3) Insert with values (123,cast('2008-04-09 03:01:30.610610' as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'),NULL); ==> SUCCESS

As per my understanding, since there is no UPI, the entire record_set (all columns) has to be checked for duplicate and since in the above case (2), the time component of timestamp field is different, the insert should have been successful. Please correct if there is a flaw in my understanding.
 
Thanks.

1 REPLY 1
Junior Contributor

Re: SET Table Duplicate Error

This seems to be related to Daylight Saving Time. 

Your session is set to a time zone with DST and '2008-03-09' is the Sunday when time shifted forward from 2 AM to 3 AM, thus '02:01:30' didn't exist and is modified to '03:01:30'.

Try switching to a time zone without DST, e.g. SET TIME ZONE -5, of course you should know where those timestamps are originally coming from.