Oracle DBを使ったシステムで、時間（ミリ秒まで）をキーにしているテーブルで「キーの重複エラー」が出てプログラムが落ちるという障害が出たので、ちょっと調べてみた。
Oracle でミリ秒まで管理する場合のデータ型は DATE ではなく TIMESTAMP となる。精度はナノ秒（秒数以下（小数秒） 9桁）まで。
テストで、TIMESTAMP型の項目をキーとしたテーブルを作成する。
CREATE TABLE t_test1 (id INTEGER,udate TIMESTAMP,PRIMARY KEY (id, udate))
id と udate の組み合わせでユニークでないといけないテーブルだ。
なので、
INSERT INTO t_test1 (id, udate) VALUES(1, '2026-07-07 11:00:00.111111');
を 2回実行しようとすると、
ORA-00001: 一意制約(TESTER.SYS_C008227)に反しています
のエラーとなる。
INSERT INTO t_test1 (id, udate) VALUES(1, '2026-07-07 11:00:00.111222');
であれば、ミリ秒以下（小数秒 4～6桁目のマイクロ秒）の値が違うので登録はできる。
ミリ秒（秒数以下 3桁のみ）までの指定での INSERTでは、マイクロ秒に 000 を補完するので、
INSERT INTO t_test1 (id, udate) VALUES(1, '2026-07-07 11:00:00.111');
これも（内部的には 11:00:00.111000 という時間になり）通る。
SELECT 時に書式を指定しなければ、TIMESTAMP の項目はミリ秒までしか表示しない。
SELECT * FROM t_test1 WHERE udate >= '2026-07-07 11:00:00.111';
例えばマイクロ秒まで表示したければ、小数行を 6桁表示するようにフォーマットを指定する。
SELECT id, TO_CHAR(udate, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM t_test1WHERE udate >= '2026-07-07 11:00:00.111';
ちなみに、CREATE TABLE 時に TIMESTAMP とだけ指定すると、マイクロ秒（小数秒 6桁）までの管理となる。
そのため、
INSERT INTO t_test1 (id, udate) VALUES(1, '2026-07-07 11:00:00.111222333');
は、実際には 111222 までしか見ないので（ナノ秒部の 333 は無視する）、
ORA-00001: 一意制約(TESTER.SYS_C008227)に反しています
となる。ナノ秒まで扱うのであれば、
CREATE TABLE t_test1 (id INTEGER,udate TIMESTAMP(9),PRIMARY KEY (id, udate))
と、TIMESTAMP の後ろに少数桁数の(9)を設定する必要がある。
ところで、最初に書いた障害は、CSVファイルに「ミリ秒まで」書かれたデータを読込み DB に登録するときに発生したもの。元のデータがミリ秒までなので、TIMESTAMP の指定がどうこうで解決する問題ではない。
結局、時間をキーにせず、自動採番した番号などをキーにするしかない。
CREATE TABLE t_test1 (num NUMBER GENERATED ALWAYS AS IDENTITY,id INTEGER,udate TIMESTAMP(9),PRIMARY KEY (num))
とかね（num はシーケンスを使ってユニークに自動採番される）。
いや、あれこれ TIMESTAMP を試していたのは何なの？って話なんだけど、今後無駄になる知識じゃないからね。勉強、勉強。
ちなみに TIMESTAMP は、PostgreSQL や MtSQL などで微妙に仕様が違うからね。使うときは注意ですぞ（笑）
コメントする