unique index on a timestamp column - only works to the second

A unique index on a timestamp column truncates the milliseconds from the timestamp and can result in an unexpected unique constraint violation.



CREATE TABLE reh_temp
AS
SELECT SYSTIMESTAMP tstamp FROM dual;

CREATE UNIQUE INDEX reh_tmp_pk ON reh_temp(tstamp)
;
INSERT INTO reh_temp
SELECT SYSTIMESTAMP
FROM dual
UNION
SELECT CAST(SYSTIMESTAMP AS date) FROM dual;

SELECT * FROM reh_temp