SQLite3でUNIQUE制約とCREATEで作成したインデックスに違いがあるのか調べてみた
2021/12/07 Tue.
UNIQUE制約を定義すると自動的にインデックスが作成されますが、自分で「CREATE INDEX」した場合と違いはないのか？ちょっと気になったので速度を調べてみることに。
category: データベース
PostgreSQLの格納データサイズによって速度低下する問題
2020/10/26 Mon.
Gigazinさんに非常に興味深い記事「PostgreSQLには「中途半端なサイズ」のデータを格納しないほうがいい」があったので備忘録。
なんでも、PostgreSQLは「固定長のページ」という概念の中にテーブルを格納して管理しているようです。
そのページに収まらない大きなデータを格納する場合は「TOAST」という機能が自動的に稼働してデータを専用のテーブルに分割格納しているようです。
「TOAST」のことは「PostgreSQL 12.3文書 第68章 データベースの物理的な格納 68.2. TOAST」をご覧ください。
で、興味深いのは、
このTOAST機能を利用するほど大きなデータを扱うとPostgreSQLのパフォーマンスが低下する。
のではなく、
TOAST機能を必要としない「ぎりぎりサイズ」程度のデータを格納するとパフォーマンスが低下する。というのがなるほどと思ったわけです。
データベースは奥が深すぎて私には使いこなせないです。
(´・ω・`)
これからも「便利な格納庫」程度の認識で使わせてもらいます。
なんでも、PostgreSQLは「固定長のページ」という概念の中にテーブルを格納して管理しているようです。
そのページに収まらない大きなデータを格納する場合は「TOAST」という機能が自動的に稼働してデータを専用のテーブルに分割格納しているようです。
「TOAST」のことは「PostgreSQL 12.3文書 第68章 データベースの物理的な格納 68.2. TOAST」をご覧ください。
で、興味深いのは、
このTOAST機能を利用するほど大きなデータを扱うとPostgreSQLのパフォーマンスが低下する。
のではなく、
TOAST機能を必要としない「ぎりぎりサイズ」程度のデータを格納するとパフォーマンスが低下する。というのがなるほどと思ったわけです。
データベースは奥が深すぎて私には使いこなせないです。
(´・ω・`)
これからも「便利な格納庫」程度の認識で使わせてもらいます。
category: データベース
PostgreSQLで列項目（文字列）と列項目（文字列）のパターンマッチをさせるSQL
2019/11/28 Thu.
あるテーブルに「A」と「B」という文字型項目があったとして、「A」が「B」に含まれているかの文字列パターンマッチを行うSQLがすぐ忘れそうなので書いておく。
です。
「%」は0文字以上の並びとの一致を意味します。詳しくはPostgreSQLマニュアルのパターンマッチ「LIKE」をご覧ください。
「||」は文字列の結合です。
select * from テーブル名 where B like '%' || A || '%';
です。
「%」は0文字以上の並びとの一致を意味します。詳しくはPostgreSQLマニュアルのパターンマッチ「LIKE」をご覧ください。
「||」は文字列の結合です。
category: データベース
SQLITE3でUNIXタイムスタンプを日付文字列に変換する
2019/10/29 Tue.
SQLITE3で日付を扱う際にテキスト型ではなく数値型でUNIXタイムスタンプを格納している場合があります。
それを、日付文字列形式で取得する方法です。
それを、日付文字列形式で取得する方法です。
category: データベース
SQlite3 でソートするときにアルファベットの大文字と小文字を区別したくない
2019/04/12 Fri.
そんなとき、COLLATE nocase を付けてあげたら解決。
select 項目 from テーブル名 order by 項目 COLLATE nocase;
select 項目 from テーブル名 order by 項目 COLLATE nocase;
category: データベース
PostgreSQLで16進数、2進数を扱う場合の表記
2019/01/26 Sat.
16進数の場合「x」、2進数の場合は「b」を頭に付ける。
x'a' = b'1010' = 10
しかし、これだけだと10進数として計算できないので「::integer」を付ける。
select x'f'::integer + 5;
「20」と表示されます。
x'a' = b'1010' = 10
しかし、これだけだと10進数として計算できないので「::integer」を付ける。
select x'f'::integer + 5;
「20」と表示されます。
category: データベース
SQLite3での文字列連結と数値の文字列結合
2017/10/26 Thu.
SQLite3で文字列結合の関数はなんだろう？と思ったけど関数じゃなくて「||」という演算子で結合できる。
数値はそのまま文字列結合できるのかな？と思ったら、そのままできた。
（例）
select 100 || '*' || 200;
⇒100*200
もちろん、数値のカラムを指定しても連結できる。
数値はそのまま文字列結合できるのかな？と思ったら、そのままできた。
（例）
select 100 || '*' || 200;
⇒100*200
もちろん、数値のカラムを指定しても連結できる。
category: データベース
PostgreSQLで「invalid input syntax for type timestamp with time zone」
2016/07/12 Tue.
というエラーが出た。
どうやってこのエラーが出たかと言うと、
select CURRENT_TIMESTAMP-'24 hours';
でエラーになりました。
正しくは、
select CURRENT_TIMESTAMP-interval '24 hours';
と書かなくちゃいけない。
これが足し算の場合
select CURRENT_TIMESTAMP+'24 hours';
は、エラーにならないんだなこれが。
まあ、
select CURRENT_TIMESTAMP+interval '24 hours';
と、「interval」を書くクセを付けた方が良いみたい。
ということで、思い込みによるバグは見つけるのが難しい一例でした。
どうやってこのエラーが出たかと言うと、
select CURRENT_TIMESTAMP-'24 hours';
でエラーになりました。
正しくは、
select CURRENT_TIMESTAMP-interval '24 hours';
と書かなくちゃいけない。
これが足し算の場合
select CURRENT_TIMESTAMP+'24 hours';
は、エラーにならないんだなこれが。
まあ、
select CURRENT_TIMESTAMP+interval '24 hours';
と、「interval」を書くクセを付けた方が良いみたい。
ということで、思い込みによるバグは見つけるのが難しい一例でした。
category: データベース
64ビット環境でのデータベースの符号付INTEGER型整数の範囲
2015/08/26 Wed.
64ビット環境ではPHPの符号付INTEGER型整数の上限は 9223372036854775807 になります。
PostgreSQLはどうなるんだろう？と思って調べてみたら 2147483647 でした。
PostgreSQLの場合、OSのビット数に関係なく「型宣言」で整数の範囲が決まるようです。
INTEGER → 2147483647
BIGINT → 9223372036854775807
気を付けなくちゃいけないのはPHPでビット操作をする場合。PostgreSQLのINTEGER型をPHPの変数に格納した時点で64ビット整数になります。
INTEGER型で「-1」は「0xffffffff」ですが、PHPの変数では「0xffffffffffffffff」になる。
注意が必要です。
ついでにSQlite3も調べてみました。
64ビット環境でのINTEGER型の上限は 9223372036854775807 です。
PostgreSQLはどうなるんだろう？と思って調べてみたら 2147483647 でした。
PostgreSQLの場合、OSのビット数に関係なく「型宣言」で整数の範囲が決まるようです。
INTEGER → 2147483647
BIGINT → 9223372036854775807
気を付けなくちゃいけないのはPHPでビット操作をする場合。PostgreSQLのINTEGER型をPHPの変数に格納した時点で64ビット整数になります。
INTEGER型で「-1」は「0xffffffff」ですが、PHPの変数では「0xffffffffffffffff」になる。
注意が必要です。
ついでにSQlite3も調べてみました。
64ビット環境でのINTEGER型の上限は 9223372036854775807 です。
category: データベース
