読者です 読者をやめる 読者になる 読者になる

ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQLの検査制約におけるNULLの扱い

プログラミング

はじめに

PostgreSQLを基準とした資格試験の問題集「徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応」にて、以下の検査制約を設定したテーブルに対して、値にNULLを含んだINSERTが成功するか?という問題がありました。

CREATE TABLE points (
  col1 INTEGER PRIMARY KEY,
  col2 INTEGER,
  CHECK (
  col1 > 0
  AND col2 < 0
  )
);

INSERT INTO points
VALUES
  (3, NULL);

答えは「成功する」なのですが、これを理解するには検査制約におけるNULLの扱いがポイントとなります。
本エントリで解説します。

NULLと比較述語

NULLと比較述語について「達人に学ぶSQL徹底指南書」の「1-3 3値論理とNULL」からまとめます。

NULLに比較述語を適用した結果は常に真理値unknownとなります。これはNULLが値でも変数でもなく、値を対象とする比較述語を適用できないためです。

これに伴い、以下の式は全部unknownとなります。
1 = NULL
1 > NULL
1 < NULL
1 <> NULL
NULL = NULL

真理値には以下の優先順位があります。
ANDの場合:false > unknown > true
ORの場合 :true > unknown > false
"true AND unknown"なら結果はunknownです。
"true OR unknown"なら結果はtrueです。

PostgreSQLの仕様

前節で説明したNULLと比較述語の関係はPostgreSQLについても成り立ちます。

入力のどちらかがNULLの場合、通常の比較演算子は真や偽ではなく(「不明」を意味する)nullを生成します。 例えば7 = NULLはnullになります。7 <> NULLも同様です。

9.2. 比較関数および演算子

そして、検査制約のNULLの扱いについて以下の説明をしています。

検査制約では、検査式が真またはNULL値と評価された場合に、条件が満たされることに注意して下さい。 ほとんどの式は、演算項目に一つでもNULLがあればNULLと評価されるので、検査制約では制約対象の列にNULL値が入るのを防げません。

5.3. 制約

検査制約でNULLを評価する

冒頭のSQLを再掲します。

CREATE TABLE points (
  col1 INTEGER PRIMARY KEY,
  col2 INTEGER,
  CHECK (
  col1 > 0
  AND col2 < 0
  )
);

INSERT INTO points
VALUES
  (3, NULL);

INSERT実行時、検査式は以下の通り評価されます。
(3 > 0) AND (NULL < 0)
= true AND null
= null

PostgreSQLは検査式がNULLと評価された場合も条件を満たすため、このINSERTは成功します。

非NULL制約(NOT NULL)

PostgreSQLで列にNULL値を含ませないためには非NULL制約(NOT NULL)が推奨されています。

非NULL制約は常に列制約として記述されます。 非NULL制約はCHECK (column_name IS NOT NULL)という検査制約と機能的には同等ですが、PostgreSQLでは、明示的に非NULL制約を作成する方がより効果的です。

5.3. 制約

参考

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

SQLを書くときに常に手元へ置いておきたい一冊。
こんな時はどうなるのだっけ?というヒントがまとめられています。

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

この問題集を解くことで、今回のエントリのようにわかっているようでわかってないことに気づくチャンスとなるかも。

OSS-DB Silver [OSDBS-01] 受験対策教材集

プログラミング インフラ

はじめに

OSS-DB Silver [OSDBS-01] 受験対策の教材はあまり多くありません。ですが、よく探してみると、自習で役立つ資料が公開されていたりします。
このエントリでは私が受験対策のために実際に使い、参考になったものをご紹介いたします。

テキスト

OSS教科書 OSS-DB Silver

OSS教科書 OSS-DB Silver

OSS教科書 OSS-DB Silver

試験対策の最初の1冊としてPostgreSQLの全体像を知るためによいと思います。最後の章に模擬試験が付いています。
ただ、出題範囲の全てをカバーはできておらず、問題集で取り上げられた内容が本書には説明のないことがあります。また、出版されてから日が経っていることもあり、その後のアップデートをカバーできていません。
受験対策としても、そして業務に活かすためにも、この1冊を足がかりに公式ドキュメントや関連記事を読み込んだり、実際に環境を構築して触ってみることが必須です。

PostgreSQL日本語ドキュメント

PostgreSQL日本語ドキュメント
日本PostgreSQLユーザ会が翻訳・公開しています。
全てを読む必要はありませんが、テキストや問題集でわからなかったところはこまめに調べましょう。
ドキュメントはバージョン毎に用意されていますが、Silverの出題範囲を踏まえるとバージョン毎に大きく異なることはないと思われます。出題範囲に記載されている対応バージョンを参考にするか、悩んだら最新版でも問題ないと思われます。

OSS-DB Silverの出題範囲(対応バージョン含む)はこちらを参照ください。
出題範囲|DBスペシャリストを認定する資格 OSS-DB技術者認定試験

LPIセミナー(技術解説資料あり)

イベント・セミナー|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
OSS-DB Exam Silver 技術解説無料セミナー』が目印です。イベント詳細ページに資料がアップされています(されていない場合もあります)。
ここでポイントなのが、このセミナーは担当講師が時折入れ替わっており、それに応じて資料の内容も入れ替わっています。時間が許せば複数読んでみるのもよいかもしれません。

OSS-DB道場

OSS-DB道場|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
LPIが公開しているPostgreSQLに関するコラムです。
各コラムはさくっと読める分量ですので、隙間時間や気分転換に読んでみてはいかがでしょうか。

問題集

徹底攻略 OSS-DB Silver問題集

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

現時点にて、書店で入手可能な唯一の問題集と思われます。
テキスト付属の例題や無償公開されている問題集もありますが、出題範囲に対する網羅度や解説の丁寧さを考慮すると、この問題集をベースに学習を進めるのがよいと考えています。

解説は丁寧ですが、一部補足したほうが分かりやすい箇所があり、別エントリにてまとめました。よろしければご参照ください。
OSS-DB silver問題集 [OSDBS-01]対応 補足 - ぱと隊長日誌

LPI公式サンプル問題

サンプル問題/例題解説|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験
公式によるサンプル問題との位置付けですが、サンプルとは思えない問題数及び解説の内容となっています。
一般の問題集では出題傾向や内容に本番と差異の心配がありますが、公式であればそういった心配はないでしょう。受験前に取り組まれることをお勧めします。

ITトレメ

ITトレメ OSS-DB技術者認定試験 Silver − @IT自分戦略研究所
@ITが無償公開しているOSS-DB Silverの問題集です。
解説はあっさりした量ですが、99問が提供されているため、隙間時間の活用や最後の腕試しに良いかと思われます。

その他

LPIのサイトではOSS-DB Silver対応認定教材として、本エントリに含めていない教材についても紹介があります。よろしければご参照ください。
学習教材・教育機関のご紹介|DBスペシャリストを認定する資格 OSS-DB技術者認定試験

OSS-DB silver問題集 [OSDBS-01]対応 補足

プログラミング インフラ

OSS-DB Silver [OSDBS-01] 受験対策としてインプレスジャパンの問題集を利用されている方は多いかと思います。

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

徹底攻略 OSS-DB Silver問題集[OSDBS-01]対応 (ITプロ/ITエンジニアのための徹底攻略)

問題毎に丁寧な解説が付いていますが、解説だけで理解することが難しい箇所もあります。
本エントリではこのギャップを埋めるべく、解説若しくは関連記事の紹介を行います。

なお、私は著者・編者・発行所のいずれとも無関係であることをご了承ください。

第1章 問13

テーブルスペースについて別エントリにまとめましたのでご参照ください。
PostgreSQLのデータベースクラスタ及びテーブルスペースの関係 - ぱと隊長日誌

第4章 問15

チェック制約におけるNULLの扱いについては別エントリにまとめましたので、ご参照ください。
PostgreSQLの検査制約におけるNULLの扱い - ぱと隊長日誌

第4章 問26

継承についてはPostgreSQLドキュメントを参照ください。
5.9. 継承
上記ドキュメントには親テーブルだけを対象としたいときはONLYキーワードを利用すること、一意制約が子テーブルに継承されないために起こる制約についての記載などもあります。

第4章 問36

EXISTSを使用したサブクエリについて別エントリにまとめましたのでご参照ください。
SQLのWHERE句で用いられる相関サブクエリを理解する - ぱと隊長日誌

第10章 問1

正規化について以下の記事が分かりやすいです。ボイス・コッド正規形以降についても解説されています。
データベースエンジニアへの道(3):素早く正規形を見抜く実践テクニック (1/4) - @IT

第10章 問4

解説にマテリアライズドビューはPostgreSQL 9.0でまだ実装されていないとなっていますが、9.3で実装されました。
OSS-DB最前線~2013秋~レポート PostgreSQL 9.3の新機能を解説 - クラウド Watch

第10章 問23

NATURAL JOINは2つのテーブルで同じ名前のカラム名を使って結合を行います。

最後に、NATURALはUSINGの略記形式で、2つの入力テーブルの両方に含まれているすべての列名で構成されるUSINGリストを形成します。 USINGと同様、これらの列は出力テーブルに一度だけ現れます。 共通する列が存在しない場合、NATURALはCROSS JOINと同様に動作します。

7.2. テーブル式

第10章 問25

PostgreSQLDDLトランザクションの一部となります。Oracleとは異なるので要注意です。

PostgreSQL では、CREATE TABLE や DROP TABLE などの DDLトランザクションの一部となるため、トランザクションの途中で DROP TABLE を実行した場合でも、最後に ROLLBACK すれば、DROP したテーブルが元に戻ります。
例えば Oracle では、DDL を実行すると、その時点で「自動 COMMIT」が発生し、それ以前の更新が自動的にデータベースに反映されます。DDLトランザクションの一部にはならないので、ROLLBACK できません。

第1回 トランザクションについて|オススメ!OSS-DB情報|OSS-DB道場|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験

第10章 問30

解説で紹介されたtrim関数について補足します。

関数:
trim([leading | trailing | both] [characters] from string)
説明:
characters(デフォルトでは空白)で指定された文字のみを含む最も長い文字列を、stringの先頭、末尾、あるいはその両方から削除します。

9.4. 文字列関数と演算子

"characters"と複数形である通り、削除文字は複数指定できます。また、削除文字の順序は問いません。あくまで文字単位で評価されます。以下に実行例を挙げます。

select trim(both 'abc' from 'abccbaPOSTabccbaGRESabccba');
btrim
POSTabccbaGRES

第10章 問34

CREATE FUNCTIONのパラメータ"STRICT"についての説明を引用します。

RETURNS NULL ON NULL INPUTもしくはSTRICTを指定すると、関数の引数に1つでもNULLがある場合、常にNULLを返します。 このパラメータが指定されると、NULL引数がある場合、関数は実行されません。 代わりに、NULLという結果が自動的に与えられます。

CREATE FUNCTION

更新情報

2017/03/19

  • 第4章 問15の解説を追加しました。