PostgreSQL 公式の語るやっちゃいけないことリストがおもしろかった

なにげなく『週刊Railsウォッチ（20200115後編）』を読んでいたところ、

ってのが紹介されていたので読んだ。

BETWEEN を timestamp に対して使っちゃいけない話

これの『Don't use BETWEEN (especially with timestamps)』の項目が特に勉強になって、
項目見たときは「え、よく使ってるけどなんでダメだろう」と気になるも、解決法を見て納得。

記事では

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

でなく

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

を使いなさいと語られている。
悪い方のやり方だと 2018-06-08 00:00:00 が含まれてしまうためだ。

BETWEEN で 00:00:00 が含まれるのは知っている人が多いから、
Rails で例えば以下のように書かれているのを見たことがあった。

User.where(created_at: Time.parse("2019-12-01 00:00:00")..Time.parse("2019-12-01 23:59:59")).count

23:59:59 を終わりに指定するというのは、 Rails に限らずそこそこ見慣れた書き方だと思う。

ところがこれはミリ秒が考慮されていない。

MySQL の場合 v5.6 より前はミリ秒対応がされていなかったのでこれでも良かったけれど、
v5.6 になってミリ秒対応をしたため ( https://hacknote.jp/archives/2522/ )
対応するカラムでは 23:59:59.0001 から 23:59:59.9999 の間を無視してしまう。

というわけで、以下のように書くのがベターとなる。

User.where("created_at >= ?", Time.parse("2019-12-01")).where("created_at < ?", Time.parse("2019-12-02")).count

……まあ、こういう期間集計ってだいたい社内向けにデータを出すためのクエリなので、
そんなに大きな問題にならなかったりするけど。

Rails であれば BETWEEN を使うこんな書き方もいちおうアリ。

User.where(created_at: Date.parse("2019-12-01").beginning_of_day..Date.parse("2019-12-01").end_of_day).count

これは Time クラス及び TimeWithZone の end_of_day がマイクロ秒まで考慮されているため。

Date クラスの end_of_day は in_time_zone で TimeWithZone クラスに変換されたのちに end_of_day メソッドを呼び出しているようです。
https://github.com/rails/rails/blob/v5.2.4.1/activesupport/lib/active_support/core_ext/date/calculations.rb#L84-L87

ただしこれは罠があって、MySQL では範囲外の秒数が丸められるため、
結果 2019-12-02 00:00:00 が含まれる場合があります。
（参考: https://techlog.voyagegroup.com/entry/2015/04/09/162629 ）

また、Rails v5.1.7 以前の DateTime クラスの end_of_day メソッドはミリ秒以下が考慮されていません。
Time クラスと同じメソッド名なのにややこしいです。

結果、やはり BETWEEN を timestamp 相手に使うのは得策でなく、

User.where("created_at >= ?", Time.parse("2019-12-01")).where("created_at < ?", Time.parse("2019-12-02")).count

のような書き方をするのがベターと言えるようです。
あまり Rails っぽい書きぶりではないけど。

timestamptz を使えの話

timestamp でなく timestamptz を使えという話も面白かった。
PostgreSQL ってそんな型もあるのか、という驚き。

でも Rails の migration で作れなくね？と思ったら
https://medium.com/@frodsan/activerecord-better-native-types-mappings-for-postgresql-b5391d14ea68 によると

t.column :updated_at, :timestamptz, null: false

のような書き方でいけるらしい。なるほどねーー！

今動かしてるもので変更するのは怖いけど、新しいWebアプリを作るとき（かつ、PostgreSQLでずっとやっていくと確定しているとき）は
考えてみてもいいかも。

文字数制限付きの varchar はあまり使用しないでの話

t.string :hogehoge, null: false, limit: 255, default: ""

こんな感じのスキーマ定義を見ることは多かったので、
「DBのサイズ抑えたいし limit は大事だよな〜」と思って、私も limit はよく付けてた。

でも、PostgreSQL としては「安直にそういうことしないで！」と書いている。
逆に「意味あるならOKよ」っていうところ。

記事内の例で書かれている「名字を20文字以内で設定した場合、Hubert Blaine Wolfeschlegelsteinhausenbergerdorff さんが登録しようとしたときに困るよ！」って話がちょっとおもしろかった。
ググったら正確には

Wolfeschlegelsteinhausenbergerdorffwelchevoralternwarengewissenhaftschaferswessenschafewarenwohlgepflegeundsorgfaltigkeitbeschutzenvorangreifendurchihrraubgierigfeindewelchevoralternzwolfhunderttausendjahresvorandieerscheinenvonderersteerdemenschderraumschiffgenachtmittungsteinundsiebeniridiumelektrischmotorsgebrauchlichtalsseinursprungvonkraftgestartseinlangefahrthinzwischensternartigraumaufdersuchennachbarschaftdersternwelchegehabtbewohnbarplanetenkreisedrehensichundwohinderneuerassevonverstandigmenschlichkeitkonntefortpflanzenundsicherfreuenanlebenslanglichfreudeundruhemitnichteinfurchtvorangreifenvorandererintelligentgeschopfsvonhinzwischensternartigraum

という598文字の名字らしい。長いよ、長すぎるよ！(笑)
https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelsteinhausenbergerdorff_Sr.

ところで、Postgres では（文字数制限なしの）ただの varchar と text はほとんど同じ挙動だそうな。
https://lets.postgresql.jp/documents/technical/text-processing/1

MySQL の場合は昔は TEXT が VARCHAR よりだいぶ検索遅かったらしいけど、
今はどのくらい両者に違いがあるのか。

varchar は text と違って SQL の標準の型らしいので、
とりあえず varchar 使っておけば安心かな。

なんだか想定より長い記事になってしまいましたが、
こんなところで最後にオススメの本を貼っておしまい。
BETWEEN の話は考えさせられたなあ……。