データベースのカラムにタイムスタンプ型やDATE型ではなくcharacter varyingやvarcharを指定した時に時間だけを入力したいことがありました。(ex.12:00)
そんな時はどうすればいいのかと重い、調べてみました。
制約を使って時間(ex.12:00)だけ入力できるようにする
制約を使うと正規表現を使用して数値のみ入力可能などインサートやアップデート時に値に条件をつけることができます。
制約とは
制約とはテーブルに格納するデータの種類を限定するための方法です。
型だけではまかないきれない制御を制約を加えることで実現します。
有名どころでいくと not null (nullを許容しない)、や UNIQUE (一意の値のみ許容)などがあります。
CHECK (検査制約)
制約の中でも検査制約(CHECK)という制約を使います。CHECK制約とも呼ばれたりします。
例
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
正規表現を使ったhh:MMのフォーマット
時間 hh:MM に対応した正規表現は以下になります。
^([0-1]?[0-9]|2[0-3]):[0-5][0-9]$
00:00から23:59までの時間が入力可能です。
POSIX正規表現
PostgreSQLでは正規表現を使用する際に以下の記号をつけます。
| 演算子 | 説明 |
|---|---|
| ~ | 正規表現に一致、大文字小文字の区別あり |
| ~* | 正規表現に一致、大文字小文字の区別なし |
| !~ | 正規表現に一致しない、大文字小文字の区別あり |
| !~* | 正規表現に一致しない、大文字小文字の区別なし |
制約を使って時間(ex.12:00)だけ入力のサンプルコード
それでは、実際にSQLを書いてみます。
create table time_detail (
noticeId character varying(20),
time character varying(20) CHECK(time ~* ‘^([01][0-9]|2[0-3]):[0-5][0-9]+$’) not null
);
INSERT INTO time_detail VALUES( ‘001’,’12:00′ );
Select * from time_detail
結果
| noticeid | time |
|---|---|
| 001 | 12:00 |
おまけ hh:MM:ss
ちなみに秒まで対応させようとした場合(hh:MM:ss)は以下の正規表現になります。
^([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]
おわり
制約は正規表現が絡むと途端に難しいですね。正規表現すらすらかける人本当にすごいなと思います。参考元からコピペしてるだけじゃよくないですね。正規表現を使うたびに一応記号の意味を調べるのだけれど全く入ってこないのは頭が悪いからなのか。。
参考
https://www.postgresql.jp/document/9.4/html/ddl-constraints.html
https://www.postgresql.jp/document/9.4/html/functions-matching.html
https://stackoverflow.com/questions/7536755/regular-expression-for-matching-hhmm-time-format/7536768
