目次
はじめに
OSS-DB Silverに合格しました！
勉強時間は2週間ほどで、最初からなめずにしっかり勉強しました（笑）
意外と勉強方法の記事が世にでていなかったので、記事として残しておこうと思います。これから受けようと思っている方はぜひ参考にしてください！
受験前のスペック
- 社会人3年目
- 営業1年、データエンジニア1年~現職
- 開発の知識はほとんどない、簡単なアプリ作れるぐらい
- サーバーサイドもクラウドもうす〜い知識
- 触ったことあるDBは、MySQL、BigQueryぐらい
- 持ってる資格は、GAIQのみ
というスペックです（笑）データエンジニアとして働いてきたものの今まで資格とは無縁な生活を送ってきました。オープンソースのデータベースもMySQLに触ったことがあるぐらいでPostgreSQLはほとんど知らない状態からスタートです。
そもそもOSS-DB Silverとは？
すでに受けようとしている方はご存知と思いますので、スルーして勉強方法に飛んでください。
OSS-DB Silverとは一言でいうと
「オープンソースのDB使ってデータベースの構築と運用ができることを認定する」
みたいなイメージです！
出題されるデータベースは、PostgreSQLでSQLの問題と運用、一般知識の問題が出題されます。
ただ、出題項目は公式サイトに詳細に記載してくれていて、サンプル問題もたくさんあるのでちゃんと勉強したら、絶対合格できます！安心してください！
ちなみに、公式サイトは以下です。
https://oss-db.jp/outline/silver
出題項目通りに勉強を進めていくことをおすすめします。逆をいえば、ここに記載されていること以外はでないです。
受験結果
う〜ん。結構間違えてますね。想定より全然取れなかった感じです。
唯一の救いは、「開発/SQL」の箇所が9割取れていたことぐらいです。ここが取れてなかったらデータエンジニア失格ですね。。。
勉強方法
では勉強方法を紹介していきます。
ステップとしては、3ステップに分けて勉強しました！
- OSS-DB Silverの教科書を1周読み、要点をEvernoteにまとめる
- 公式サイトのサンプル問題集を1周する
- Ping-tの問題500問をひたすら解き、完璧にする
一つずつ解説します。
OSS-DB Silverの教科書を1周読み、要点をEvernoteにまとめる
最初が一番肝心です。
テスト範囲は公式サイトにでているので、その項目をEvernoteに丸コピします。
項目ごとに教科書を読んでいき、要点をnoteにまとめて雰囲気を感じ取るのがいいです。ここで全部覚えようとすると時間かかるので、あくまで雰囲気でOKです。
公式サイト↓
https://oss-db.jp/outline/silver
教科書↓
こちらの教科書は、出題範囲どおりに作られているので、要点をまとめる際にも非常に楽にまとめることができました。
このページの最後に私がEvernoteにまとめたメモも記載しておきます。最初はこちらを見ながら問題を解くと頭に入ってきやすいと思います。ぜひ、使ってください。
公式サイトのサンプル問題集を1周する
公式サイトに「運用管理」「一般知識」「開発/SQL」ごとにサンプル問題が約300問ぐらい用意されています。
https://oss-db.jp/measures/sample
教科書を一通りまとめた後に、
「どういう風に問題として出されるのか把握するため」に問題を1周します。
最初はやはりわからない問題も多いかもしれませんが、こちらも雰囲気を掴むことが重要です。1問づつ解いていき、こういう感じで出題されるのか〜ぐらいでOKです。
ただし、1問1問暗記するのではなく、回答の説明を読んで理解してください。
ping-tの問題500問をひたすら解き、完璧にする
ここに一番時間を使いましょう。
ping-tというサイトにOSS-DBの問題500問用意されています。
さらに本番の出題内容と近い感じで出題されます。言ってしまえば、ping-t完璧に解けるようになれば、受かります！！
2000円と有料ですが、これは必ずやるべきです。
ちなみに私はレベル38までやりました。
補足
以上の3ステップをやれば受かります。
補足として、時間があればこれもやるとさらに自信がつく項目を紹介します。
PostgreSQLをインストールして試す
実際にPostgreSQLをPCにインストールしてコマンドを試してみるとより理解が深まると思います。
私も実際にインストールしてこうやって動くのか、これはこういうエラーになるのかと想像を膨らませたりしながら、勉強してました。
本読んでるだけとか問題解いてるだけだと、私の場合眠くなってきてしまうので、、、、。
黒本問題集を1周する
これは私もほとんどやる意味はなかったかなと思うことですが、ここも完璧に理解すれば100点も夢じゃなかったかななんて。。。
最後に
資格受験は意味ないとか、実務で使わないじゃんとかたまにおっしゃる方もいますが、私的には今の自分の実力を図るのに最適だなと改めて実感しました。
しかもなんだかんだ言って能力があることの証明にもなりますからね。
ただ、本当に実務で使わないような資格は私も取りません。実務で使うような資格試験を受験して、技術の幅を広げていくのがいいですね。
これからもさらに鍛錬していきます。
付録
私がEvernoteにまとめたメモを以下に記載します。
ぜひ参考にしてください！
一般知識 16%
---
OSS-DBの一般的特徴 【重要度4】
・OSS-DBおよびOSS一般のライセンス
→GPL、LGPL、Apacheライセンス、BSDライセンス、MITライセンス
BSDライセンスをベースにしたPostgreSQLライセンスで公開されている
著作権表示、使用許諾の内容、免責事項、無保証に関する記載
→商用/非商用問わず無償で利用可能で、改変も自由にでき、著作権表示とライセンス条文自身を表示することで配布も可能
・OSS-DBのコミュニティ
→「PostgreSQL Global Development Group」
ユーザー会もある。。Web会員の会費は無料ですが、正会員には年会費が発生する。
・活動内容
→どのような機能を盛り込むか、どういうソースコードで実装するかなどを考え、実装する
ユーザー会は、Postgresqlの普及促進を目的にセミナーやイベントの企画運営、ドキュメントの翻訳を行っている。
・参加方法
→だれでも参加可能。postgresql.org
・メジャーバージョン
→9.0.4→先頭の２つがメジャーバージョン
メジャーバージョンの変更は、大きな機能変更
旧バージョンのデータファイルを使用できない可能性が高い
論理バックアップ。
1年に一回程度。
異なるバージョンで互換性がない場合がある。
・マイナーバージョン
→9.0.4→末がマイナーバージョン
10.0移行は、x.zのみになっており、9.4を例とすると9がメジャーバージョン、4がマイナーバージョン
マイナーバージョンの変更は、不具合やセキュリティ対策。通常は3ヶ月ごとにリリース。
サポート期間はメジャーバージョンのリリースから5年
バージョンアップはサーバーを停止させる
・リリースサイクル
→アルファ、ベータ、RC(安定版直前のリリース)
・リリースノート
→メジャーバージョン、マイナーバージョンの各々がバージョンアップし、リリースされるたびに公開される。
過去のリリースノートは見返せる
・サポートポリシー
→マニュアルはソフトウェアに同梱されており、オンラインでもみれる。
・バグ報告
→開発コミュニティのメーリングリストもしくは、WEBサイトから報告できる
・最初のバージョンは6.0
・メジャーバージョンが複数同時公開されていることもある。
・最終的な意思決定は、開発コミュニティの中心を担う、ごく数名のコアメンバが行う
・イベントは世界各地で行われている。本部はない。
・メーリングリストの公用語は英語。英語を翻訳している。
リレーショナルデータベースに関する一般知識 【重要度4】
・リレーショナルデータベースの基本概念
→データを一元管理するためのソフトウェア。データの安全性や一貫性を保証する。
・データべース管理システムの役割
→・データの機密保護を実現
→アクセス権限とデータの暗号化
・トランザクション制御
→排他制御によるデータ不整合の防止
・データの整合性を維持
→同時実行制御、排他制御によるデータ登録時や形式チェック時の整合性を維持
・障害からの安全な復旧
→バックアップとログで完全復旧が可能
・アプリケーションプログラムのためのインターフェースを提供
→必要なデータに必要なときにアクセスできる仕組みを提供
・データモデル
→対象世界：データベースを構築する範囲
対象世界をモデル化：データモデル
データモデルを作成：データモデリング
概念データモデル：データベースを構築する範囲を集めて、概念的に表現したモデルのこと。ER図が該当する
論理データモデル：データとデータの関係を表現したモデルのこと。
・階層モデル：階層構造を持つデータを表現するときに使う。親子
・ネットワークモデル：階層構造を持つデータかつ親が複数のとき
・関係モデル：表構造で表現。一般的に用いられるモデル。関係：関係
行＝タプル、組、ロー、列=アトリビュート、カラム、属性
列の範囲=定義域(ドメイン)
列の数を関係の次数、行の数を関係の基数という
・SQLに関する一般知識
→データを定義したり、操作したりする言語、SQL86からスタート
・SQLの分類(DDL/DML/DCL)
→DDL(Data Definition Language)：データ定義言語：テーブルやインデックスの作成、変更、削除を行う。CREATE, ALTER, DROP
DML(Data Manipulation Language)：データ操作言語：データの参照・追加・更新・削除を行う。SELECT, INSERT, UPDATE,DELETE
DCL(Data Control Language)：データ制御言語：権限の設定・取り消し、データの更新の確定・取り消しを行う。GRANT,REVOKE,COMMIT,ROLLBACK
・データベースの設計と正規化
→設計
①概念データモデルを作成
②導入するDBMSに合わせて、論理データモデルに変換する
ようするに、なんのデータが必要か、どういう範囲でデータがあるか決めて、どのデータがどれと紐づくようにしないといけないか決める
正規化：更新時異常(データに一貫性がない、重複している等)がおきないようにテーブルを作る
主キー：候補キーの中からNULLがないものを選定
候補キー：行を一意に識別できる単独の属性
非キー属性：いずれの候補キーにも含まれない属性
関数従属：ある属性Xの値が決まれば、別の属性のYの値も決まる=YはXに関数従属する
非正規形：関係のドメインが単純でない関係。すべて一つのテーブルで表現されている
第一正規形：非正規形を表からテーブルの構成にしたもの。非正規形から繰り返し現れる形のデータを別の表に切り離し、導出項目を削除
第二正規形：第一正規形の非キー属性を関数従属別にテーブルを分ける。複合主キーの一部に関数従属しているものを分割。
第三正規形：第二正規形から推移関数従属をなくすように分ける。第二正規形から主キー以外の項目に関数従属しているものを除く
ボイスコット正規形：第三正規形であり、関係Rのすべての候補キーについて、候補キー以外の属性がRに完全関数従属であるように分ける
---
運用管理 52%
---
インストール方法 【重要度2】
・データベースクラスタの概念と構造
→PostgreSQLの格納領域のこと
クラスタはinitdbコマンドで作成
配置場所は自由で、複数のマシンで分散管理はできない。
$PGDATAを指定しておく必要はないが、指定しておいたほうが楽。
$PGDATAがなければ、新規に作成されるが、ディレクトリは空でないといけない。空でないとエラーになる。
＄PGDATA
→データベースクラスタのこと
|-PG_VERSION：postgreSQLのメジャーバージョン番号を記録したファイル
|-base/：各データベースのサブディレクトリを格納するディレクトリ
|-1/
|-11866/
|-11874/
|-global/：データベース間で共有するデータ(ユーザー情報など)を格納するディレクトリ
|-log/ ：ログファイルを格納するデフォルトのディレクトリ
|-pg_wal：トランザクションログ(WAL)を格納するディレクトリ
|-postgresql.conf：PostgreSQLのパラメータを設定するファイル
|-pg_hba.conf：クライアントの認証情報を設定するファイル
|-postmaster.pid：起動中のPostgreSQLサーバーのPIDなどを記録するファイル
データベースクラスタ：PostgreSQLサーバー：１：１=インスタンス
→PostgerSQLのプロセス郡のこと※物理的OR仮想的サーバーのことではない。複数のPostgreSQLサーバーは使用できないし、共有もできない。パスを変えれば複数作成することができる。
データベース：テーブルやインデックスやビューなど、データベースオブジェクトの集合体。BQでいうデータセットと同等。
データベースクラスタには、デフォルトで3つのデータベースが定義されている。
template0：テンプレートデータベース
template1：テンプレートデータベース
postgres：標準付属ツールがデフォルトの接続先として使うデータベース。
通常のデータベース同様に使うことができる
・テンプレートデータベース
→PostgreSQLは、既存のデータベースから内容をコピーして新しいデータベースを作成する。
その際のコピー元となるデータベースがテンプレートデータベース(template0とtemplate1)
template1
→新しいデータべースのテンプレートとして使われる。
template1内のデータベースオブジェクトはすべて新しいデータベースにコピーされる
ので、多くのデータベースに共通で定義したいときに便利。
1を指定した場合、あとから-Eや-Lオプションを使用してcreatedbは作れない。
template0
→初期(クラスタ作成時点)のtemplate1と同じ内容のテンプレート。
template0の内容を変更することはできない。デフォルトでは誰もアクセスできない。
template0はtemplate1に登録済みのオブジェクトをコピーしたくないときに使用する。
※template0と1の初期状態はすべて同じ
テンプレートをカスタマイズする際はtemplate1を使用する。
・initdbコマンド
→指定したディレクトリにデータベースクラスタを作成するコマンド
ローカルのホスト環境でのみ実行可能
オプション
-D、--pgdata=ディレクトリ名：データベースクラスタを作成するディレクトリを指定する。
未指定の場合は、環境変数PGDATAが使われる。
-E、--encoding=エンコーディング：エンコーディングを指定する。
未指定の場合は、OSのロケールから自動的に
エンコーディングされる。基本UTF8を使用。
--locale=ロケール：ロケールを指定する。
未指定の場合は、OSのロケールが指定される。基本--no-localeにする。
ロケールはクライアント環境に依存しない
--no-locale / --locale=C：ロケールを無効にする
-U、--username=ユーザー名：作成するデータベースのスーパーユーザーの名前を指定する。
未指定の場合は、PostgreSQLの管理ユーザーの名前になる
-k、--data-checksums：データベースのチェックサム(データ破損を検出するための仕組み)を有効にする
-X ディレクトリ名、--waledir=ディレクトリ名：WALを格納するディレクトリを指定する
initdbはroot以外のOSユーザーで実行。rootユーザーでは実行できない。
管理ユーザーのみ実行することができる。
管理ユーザー：initdbでデータベースクラスタを作成したOSユーザー
データベースクラスタを管理できるユーザー
初期ユーザーはPostgreSQLの管理ユーザーと同じ名前のデータベースユーザー
データベースクラスタを作成済みのディレクトリにはinitdbに指定できない(エラーになる)
※データべースエンコーディング：文字をデータベースに格納するときにエンコーディングすること
PostgreSQLが使えるエンコーディングは、UTF8かEUC_JPのみ。SJISは使えない
クライアントエンコーディング：クライアント側が使用するエンコーディング。SJISも使える。
データベースエンコーディングとクライアントエンコーディングは自動補完される
文字セットは各データベースごとに別々のものが指定できる。
標準付属ツールの使い方 【重要度5】
・データベース管理用コマンドの使い方
→-V, --version：ツールのバージョン番号を表示
-?、--help：ツールの使用方法を表示する
・ツールからの接続
→TCP/IPもしくはUNIXドメインによる通信が可能
オプション＝-h、--host=ホスト名(接続先にPostgreSQLサーバーのホスト名を指定)
省略時PGHOST、設定されていない場合はUXドメイン
※UXドメイン=ツールを実行したホスト
-p、--port=ポート番号(接続先のPostgreSQLが接続を待ち受けているポート番号を指定)
省略時PGHOST、設定がないときはデフォルト値(5432)
-U、--username=ユーザー名(PostgreSQLに接続するときに使うユーザー名を指定)
省略時PGUSER、設定がないときはOSユーザー名=データベースユーザー名
※接続はどんなユーザーでも接続することができる。
ただし、PostgreSQLに接続するユーザーはログイン権限をもっている必要がある。
・データベースロール / ユーザの概念
→データベースに接続するには、適切な権限を持つユーザーアカウントが必要
PostgreSQLとOSでユーザー名は別々でOK
同じ名前のユーザーは作成できない
権限
・ユーザーの作成権限(CREATEROLE)：ユーザーを作成できる
・データベースの作成権限(CREATEDB)：データベースを作成できる
・スーパーユーザー権限(SUPERUSER)：すべてのことができる
・ログイン権限(LOGIN)：データベースに接続できる
・レプリケーション権限(REPLICATION)：ストリーミングレプリケーション用で接続できる
※テーブルの権限(GRANT)と、ごっちゃにならないように！
・データベースロール / ユーザの追加・削除・変更方法
CREATE ROLE / CREATE USER
→ROLEのときは、ログイン設定をWITHでしないとデフォで付いてこない。USERで作成した場合は、ログインをつけなくてもデフォでついている。
→・createuser
→データベースユーザーを作成するコマンド
createusr [接続オプション] [オプション] [ユーザー名]※-Uはいらない
・-P、--pwprompt：パスワードを設定
・-s、--superuser：スーパユーザー権限を設定する
・-S、--no-superuser：スーパーユーザー権限を設定しない(デフォルト)
・-d、--createdb：データベースの作成権限を設定する
・-D、--no-createdb：データベースの作成権限を設定しない(デフォルト)
・-r、--createrole：ユーザーの作成権限を設定する
・-R、--no-createrole：ユーザーの作成権限を設定しない(デフォルト)
・-l、--login：ログイン権限を設定する(デフォルト)
・L、--no-login：ログイン権限を設定しない
・--interactive：対話的に設定する場合に指定
・-e、--echo：createuserが実行したSQLを出力
・IN ROLE ロール名：メンバとして追加する既存のロール名を設定する
作成できるのはスーパーユーザーもしくは、ユーザー作成権限持ち
スーパーユーザーの作成はスーパーユーザーのみ
データベースユーザー名を指定しなかった場合OSのユーザー名でデータベースが作成される
※データべースユーザーは一意である必要がある
ユーザー名やホスト名のオプション設定も可能
・-U ユーザ名
接続時のデータベースユーザ名を指定します。
・-h ホスト名またはIPアドレス
接続先のホスト名またはIPアドレスを指定します。
・-p ポート名
接続先のポート番号を指定します。デフォルトは5432です。
・dropuser
→データベースユーザーを削除するコマンド
dropuser [接続オプション] [オプション] [ユーザー名]
・-i、--interactive：ユーザーを削除していいか削除前に確認
削除できるのはスーパーユーザーもしくはユーザー作成権限もち
スーパーユーザーの削除はスーパーユーザーのみ
データベースやテーブル等のオブジェクトを所有しているユーザーは削除できない
※ユーザー名を指定しなかったときは対話型指定になる
・ALTER USER / ALTER ROLE
→ユーザーの属性変更
スーパーユーザーもしくは、ユーザーの作成権限ありのユーザーのみ
RENAMEでユーザー名変更も可能
ALTER USER ユーザー名 [[with] SUPERUSER | NOSUPTERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | LOGIN | NOLOGIN | PASSWORD 'パスワード' | VALID UNTIL '日付']
・createdb
→データベースを作成するコマンド
createdb [接続オプション] [オプション] [データベース名]
・-E、--encoding=エンコーディング：エンコーディング指定
・-l、--locale=ロケール：ロケール指定
・-O、--owner=ユーザー名：所有者となるユーザーを指定。スーパーユーザーのみ。
・-T、--template=データベース名：テンプレートデータベースを指定
・-U=ユーザー名：ユーザー名を指定して、そのユーザー名でデータベースを作る
(省略の場合はスーパーユーザーが作成)
作成できるのはスーパーユーザーもしくは、データベース作成権限持ち
データベース所有者は、削除や名前の変更等データベースの変更操作可能
テンプレートを指定しない場合は、template1がテンプレートとなる
※データベース名が未指定の場合は、OSユーザーと同じ名前のデータベースが作成される
・dropdb
→データベースを削除するコマンド
dropdb [接続するオプション] [オプション] データベース名
・-i、--interactive：データベースを削除していいか確認する
削除できるのはスーパーユーザーもしくは、削除するデータベースの所有者のみ
・psql
→PostgreSQLへの接続、SQLコマンドの発行、結果の表示などを行うコマンド
psql [接続オプション] [オプション] [データベース名 [ユーザー名]]
・-l、--list：データベース一覧を表示。メタコマンド\lと同じ
・-c、--command="コマンド"：指定したコマンドを実行する。
※ただし、SQLコマンドとメタコマンドの混在はできない。
・-f、--file=ファイル名：指定したファイルからコマンドを読み込み、実行
・-d、--database=データベース名：接続するデータベースの名前を指定。
省略した場合は、接続に使うユーザーと同じ名前のデータベースに接続する
・-s、--single-step：各コマンドごとに実行するかキャンセルするかの確認を行う(シングルステップモード)
・-1、--sigle-transaction：複数コマンドを一つのトランザクションで実行する。-cオプションや-fオプションと組み合わせた場合のみ使用できる。
「=>」：一般ユーザーでの接続
「=#」：スーパーユーザーでの接続
・メタコマンド
→「\」からはじまる終了や一覧表示などの際に用いるコマンド。実行するのはPostgreSQL。
・\q：psql終了
・\l：データベース一覧
・\d パターン：指定したパターンに名前が一致するテーブル、インデックス、ビュー、シーケンスの構成情報を表示。パターンにはワイルドカードを使用。
・\d：テーブル、ビュー、シーケンスの一覧を表示
・\du：データベースユーザー一覧を表示
・\dn：スキーマ一覧を表示
・\dt：テーブル一覧を表示
・\dv：ビュー一覧を表示
・\ds：シーケンス一覧を表示
・\dS：システムカタログの一覧を表示
・\df：関数の一覧を表示する
・\dpまたは\z：テーブル、ビュー、シーケンスの一覧と、それらに設定されているテーブル単位の権限を表示する
→権限確認一覧
ユーザー名=xxx
r=SELECT、w=UPDATE、a=INSERT、d=DELETE、D=TRUNCATE、
x=REFERENCES、t=TRIGGER、arwdDxt=すべての権限、
/yyyy=この権限を設定したユーザー
・\copy：PostgreSQLとpsql間の間でテーブルをコピーする
・\password [ユーザー名]：指定したデータベースユーザーのパスワードを変更する
・\c(\correct) データベース名：既存の接続を切断し、指定したデータベースに新規接続
・\x：結果の表示形式を拡張モードに変更する
・\?：メタコマンド一覧を表示
・\h [SQLコマンド] (\help)：指定したSQLコマンドのヘルプ情報を表示する。sqlコマンドが未指定の場合は、SQLコマンド一覧を表示する
・\! OSコマンド:OSコマンドを実行できる。\! ls
・PostgreSQLの起動・停止方法
→pg_ctl
→PostgreSQLの起動、停止、状態確認などに使う管理ツール。ホスト上のみ、管理ユーザーのみ
pg_ctl サブコマンド [オプション]
・pg_ctl initdb：initdbを呼び出して、指定したディレクトリにクラスタを作成する。
initdbと処理は同じ。
オプション＝-D、--pddata=データベースクラスタ(ディレクトリ指定)
-o オプション(localeやエンコーディングを指定)
・pg_ctl start：PostgreSQLをバックグラウンドで起動
オプション＝-D、--pgdata=データベースクラスタ(起動するクラスタを指定)
-t 最大待ち時間(起動が完了するまでの待ち時間を秒単位で指定)
デフォルトでは完了するまで待つ。
時間を指定した場合ステータスは失敗になるが、
起動は引き続き処理され続ける。
・pg_ctl stop：PostgreSQLを停止する
オプション＝-D、--pgdata=データベースクラスタ(停止するクラスタを指定)
-w(停止が完了するまで待たない)
-t 最大待ち時間(停止が完了するまでの待ち時間を秒単位で指定)デフォ60秒
-m シャットダウンモード(停止方法を指定する)
・s[mart]：接続が切断させるまで待ってから停止
・f[ast]：接続を強制的に切断してから停止。デフォルト。
実行中の処理はロールバックされる。
・i[mmediate]：緊急停止。次回起動時には復旧処理が必要
※kill -9は使わないこと。不具合発生の可能性高い
コマンド実行直後から、新しいクライアントからの接続は禁止される
・pg_ctl restart：PostgreSQLを再起動する
オプション=-D、--pgdata=データベースクラスタ(再起動するクラスタを指定)
-m シャットダウンモード(停止方法を指定)
-t 最大待ち時間(起動/停止が完了するまでの待ち時間を指定)
停止が完了するまで必ず待ち、起動が完了するまで待つ。
停止されているクラスタを指定した場合、起動だけ行われる
・pg_ctl reload：PostgreSQLに設定ファイルを再読み込みさせる
オプション=-D、--pddata=データベースクラスタ
(設定ファイルを再読み込みさせるクラスタを指定)
設定ファイルは「postgresql.conf」「pg_hba.conf」
稼働中のPostgreSQLに反映させる
・pg_ctl status：PostgreSQLが起動しているかどうかを確認する
オプション=-D、--pgdata=データベースクラスタ
(起動の確認対象となるクラスタを指定)
・pg_ctl kill：プロセスにシグナルを送信する
pg_ctl kill シグナル名 プロセスID
シグナル
・HUP：設定ファイルの再読み込み
・TERM：スマートシャットダウン(pg_ctl stop -m smartと同じ)
・INT：高速シャットダウン(pg_ctl stop -m fastと同じ)
・QUIT：即時シャットダウン(pg_ctl stop -m immediateと同じ)
設定ファイル 【重要度 5】
・postgresql.confに関する以下4項目
・記述方法
→パラメータ名=設定値
・1行に１つずつ
・=か空白でつなげる
・空白の行は無視される
・#から行末までは無視される
・大文字小文字区別しない
・設定値が複数のときは''で囲む
・時間の単位：ミリ秒、秒、分、時、日があり、それぞれ ms, s, min, h, d で指定
・メモリの単位：B、kB、MB、GB、TB
メモリや時間の単位を指定しない場合はデフォの単位が指定される
・使える型は「boolean」「integer」「floating」「string」「enum」
booleanは「on, off, true(t), false(f), yes(y), no(n), 1, 0」が使用できる
※1,0は文字列であることに注意
・列挙値(enum)は、設定できる文字列がパラメータごとに限定
例)log_min_messages = ERROR か PANIC か FATAL
・各パラメータはデフォ値で最初からはいっているため、変更が必要なものだけ修正する
・各パタメータごとにPostgreSQLに反映できるタイミングが異なる
・不可(internal)：ユーザーが設定変更できないパラメータ。SHOW文で設定値確認
・起動(postmaster)」設定反映にPostgreSQLの起動が必要なパラメータ。設定変更したあとに再起動することで反映される。postgresql.confで"#(change requires restart)"とコメントされている
・再読み込み(sighup)：PostgreSQLの起動に加えて、pg_ctl reloadを使ったpostgresql.confの再読み込みにより設定反映できるパラメータ。
・スーパーユーザーのみ(suset)：PostgreSQLの起動、pg_ctl reloadによる設定値の変更に加えて、スーパーユーザーいよるSET文で変更できるパラメータ。
・いつでも(user)：PostgreSQLの起動、pg_ctl reloadによる設定値の変更に加えて、ユーザーユーザーおよび一般ユーザーによるSET文で設定値を変更できるパラメータ
・接続と認証
→listen_addresses：クライアントからの接続を監視するPostgreSQL側のIPアドレスを設定
設定されたIPアドレスに届いた接続要求のみを受けつける
※クライアント側ではなく、PostgreSQL側のIPアドレスを設定する
複数の場合は、,で区切る。*は利用可能なすべてを意味する。
空の場合は、監視しない。
パラメータ型：文字列
デフォルト：localhost
設定反映のタイミング：起動
※デフォルトではリモートクライアントは接続できない
port：クライアントからの接続を受け付けるポート番号を設定
パラメータ型：整数
デフォルト：5432
設定反映のタイミング：起動
max_connections：同時接続可能最大数を設定
パラメータ型：整数
デフォルト：100
設定反映のタイミング：起動
・クライアント接続デフォルト
→search_path：スキーマ検索パスを設定
パラメータ型：文字列
デフォルト："$user", public
設定反映のタイミング：いつでも
default_transaction_isolation：トランザクションのデフォルトの分離レベルを設定
パラメータ型：列挙値
・read uncommitted
・read committed
・repeatable read
・serializable
デフォルト：read committed
設定反映のタイミング：いつでも
client_encoding：クライアントエンコーディングを設定
パラメータ型：文字列
デフォルト：SQL_ASCII
設定反映のタイミング：いつでも
・エラー報告とログ収集
→log_destination：PostgreSQLサーバーログの出力先を設定。カンマ区切りで複数設定
パラメータ型：列挙値
デフォルト：stderr
設定反映のタイミング：再読み込み
ログ出力先：stderr-サーバーログを平文で標準エラーに出力する
csvlog-サーバーログをCSV形式で標準エラー出力に出力
この設定ではlogging_collectorをonにしなければいけない
sys_log-サーバーログをsyslogに出力
eventlog-サーバーログをeventlogに出力
logging_collector：標準エラー出力に出力されたサーバーログをファイルにリダイレクトするかどうか設定。パスは「log_directory」「log_filename」によってきまる
パラメータ：論理値
デフォルト：off
設定反映のタイミング：起動
log_directory：ログファイルを格納するディレクトリを設定。絶対パスまたは$PGDATAディレクトリに対する相対パスで指定
パラメータ：文字列
デフォルト：log($PGDATA/log)
設定反映のタイミング：再読み込み
log_filename：ログ・ファイルの名前
パラメータ：文字列
デフォルト：postgresql-%Y-%m-%d_%H%M%S.log
設定反映のタイミング：再読み込み
log_min_messages：どのレベルのメッセージをサーバーログに出力するか設定
パラメータ：列挙値(PANIC、FATAL、LOG、ERROR、WARNING)
デフォルト：WARNING
設定反映のタイミング：スーパーユーザーのみ
PANIC-致命的なエラー。すべてのセッション切断。停止。
FATAL-特定セッションで問題発生。そのセッション切断。
LOG-動作ログ
ERROR-特定のトランザクションで問題発生。そのトランザクションだけアボート
WARNING：想定外の動作に対する警告メッセージ
log_line_prefix：サーバーログの行頭に出力する内容を設定
パラメータ：文字列
デフォルト：%m[%p]
設定反映のタイミング：再読み込み
％u-データベースユーザー名
%d-データベース名
%p-プロセスID
%t-タイムスタンプ
%m-ミリ秒付きタイムスタンプ
%%-%ｍの文字そのもの
log_connection：スーパーユーザーのみ。クライアントのサーバへの接続試行をログに出力するパラメータ。デフォルトはoff
log_statement：スーパーユーザーのみ。どの SQL 文をログに記録するかを制御するパラメータで、有効な値は none、ddl、mod、all。デフォルトは none なので、何も記録されません。
log_duration：true/falseの論理値を設定し、trueに設定するとSQL文の実行に要した時間を出力。ただし、このパラメータだけ設定しても、SQL文自体は出力されません
log_min_duration_statement：整数値を設定し、指定したミリ秒以上を要したSQL文と実行時間を出力。0にすればすべて出力
※サーバーログは稼働状況の確認や問題原因の解析のために設定は必須。
デフォルトのlog_destination=stderr、logging_collector=offでは、ファイルに残らないので注意
ALTER SYSTEM
バージョン9.4において新機能として追加されたサーバーのパラメータ値を変更するSQLコマンド
ALTER SYSTEM SET パラメータ名 TO {設定値 | DEFAULT};
ALTER SYSTEM SET パラメータ名 = {設定値 | DEFAULT};
ALTER SYSTEMコマンドで設定したパラメータは「postgresql.auto.conf」ファイルに書き込まれ、「postgresql.conf」ファイルが読み込まれる際にALTER SYSTEMを優先する
・SET/SHOWの使い方
→SET文：パラメータを設定変更するSQL
SET [LOCAL] パラメータ { TO | = } {設定値 | DEFAULT}
設定反映のタイミングがスーパーユーザーのみもしくは、いつでものパラメータについて、PostgreSQLの起動中に設定値を変更する。ただし、そのセッションにのみ効果をもつ。トランザクションの開始移行にSET LOCALを実施すると変更範囲をトランザクションの実行中に限定することも可能。デフォルトはSESSION。※SET文により設定値を変更できるのは、一部のパラメータのみ。
SHOW文：パラメータの設定値を表示するSQL
SHOW{パラメータ名 | ALL}
SHOW文を実行したセッションの設定値が表示される。SET文で設定値を変更した場合、そのセッションと他のセッションとでは、SHOW文の結果が異なる場合がある。
RESETでもとに戻る
・pg_hba.confの設定方法
→どのホスト上のクライアントが、どのデータベースを使って、どのデータベースに接続するときに、どの方法で接続を認証するか指定する
local データベース名 ユーザー名 認証方式
host データベース名 ユーザー名 CIDRアドレス 認証方式
host データベース名 ユーザー名 IPアドレス ネットマスク 認証方式
・1行に1つずつ設定。
・1つのタブもしくは空白で区切られている必要がある
・空白を含めるときは””で囲む
・#が記載されている場合は＃移行の文字列をコメントとして扱う。行ではない。
クライアントからの接続受付→接続方式、データベース、ユーザー、アドレスがあるか探す→あれば認証。なければ拒否。検索は先頭から行われ、複数の行が一致した場合早いほうが使用される。
接続方式
・local：UNIXドメイン接続に対応。
・host：TCP/IP接続に対応。
データベース名：接続先のデータベース名を指定。all＝すべてに接続
ユーザー名：データベースユーザー名を指定。all=すべてに接続
認証方式：*=すべて
※接続先のデータベース名、接続に使用するユーザ名は、カンマ区切りで表記することによって複数指定できます。
CIDRアドレス：クライアントが所属するサブネットのアドレスを設定。
※クライアント側のアドレスを設定
IPアドレスとネットマスク：CIDRの代替手段としてサブネットのアドレスを指定。接続方式がhostの場合に限り、使用できる。
認証方式
・trust：接続を常に許可する
・reject：接続を常に拒否する
・md5：接続に対してパスワード認証を行う。MD5で暗号化されたパスワードを認証時にクライアントに要求
・scram-sha-256：接続に対してパスワード認証を行う。md5より堅牢。推奨。
・password：接続に対してパスワード認証を行う。平文のパスワードを認証時にクライアントに要求する。この認証方式はあまり使うべきではない。
・ident：OS とユーザ名が一致する場合のみ接続可(パスワードは要求しない)(パスワードを忘れてしまった際の対応)
→reload→alter user ... 'password' 'new password';
・ldap：LDAPサーバーを使って認証する
・cert：クライアント証明書をつかって認証する
バックアップ方法 【重要度7】
・pg_dump
→データベースを指定してバックアップを取る
-F：バックアップの出力形式を指定できる
-sあるいは--schema-only：テーブルの定義だけのバックアップを取得
-aあるいは--data-only：データだけのバックアップを取得
-d：テーブルごとに1つずつの圧縮されたファイルを作る
任意のテーブルやスキーマのバックアップもできる
pg_dump [接続オプション] [-Fp | -Fc | -Ft] [-f ファイル名] [データベース名]
※必ずしもスーパーユーザーでなくてもいいが、ほとんどがスーパーユーザーでの実行になる
GRANTやREVORKなどテーブルへのアクセス権限もバックアップできる
・pg_dumpall
→すべてのデータベース、データベースクラスタ全体のパックアップを取る
スーパーユーザーでの実行
pg_dumpall [接続オプション] [-f ファイル名]
pg_dumpallは内部的にはpg_dumpを実行している
※dumpallは平文形式のみ。平文＝スクリプト形式
dumpallのみデーターベースクラスタの共通データもバックアップできる
クラスタすべてのデータが対象だが、設定ファイルは含まれない
オプションでグローバルオブジェクトのみにすることも可能
dump オプション
-Fp：出力形式として平文を指定。デフォルト。
-Fc：出力形式としてカスタム形式を指定
-Ft：出力形式としてtar形式を指定
-f ：バックアップ先のファイル名を指定。省略は標準出力。
※ユーザーがアクセスしている場合であっても、バックアップがとれる
・psqlコマンドを使った平文形式のリストア
→平文でバックアップされたファイルは、psqlコマンドの-fオプションを使ってリストアが可能
psql [接続オプション] [-f ファイル名]
dumpallでバックアップした場合は、ロール情報もリストアされる
・pg_restoreコマンドを使った平文形式以外のリストア
→平文形式以外でバックアップされたファイルは、pg_restoreコマンドでリストアが可能
pg_restore [接続オプション] [オプション] [-d データベース名] [ファイル名]
・-d：リストア先のデータベース名を指定
指定しなかった場合、SQLコマンドが出力される
・-c、--clean：リストア前に既存のデータベースオブジェクトを削除する
・-j ジョブ数、--jobs=ジョブ数：リストア処理を同時に実行するジョブ数を指定する
・-1、--single-transaction：リストア処理を一つのトランザクションとして実行する
平文形式：SQLの羅列
※pg_dump、pg_all、pg_restoreはいずれもPostgreSQLの稼働中に実行するもの
バージョンが異なる場合にリストアができるのはpg_dump,pg_dumallのみ
・ファイルシステムレベルのバックアップとリストア
→サーバーを停止した状態でデータベースクラスタをコピーするバックアップも可能
物理バックアップ：データベース上のテーブル構造やデータとは無関係に取得するバックアップ。物理バックアップはサーバーを停止させる必要がある。論理バックアップ(pg_dumpよりも高速だが、ファイルサイズは大きい)
バックアップ時のPostgreSQLと比べてリストア先のバージョンが古い場合にリストアできない
論理バックアップ：テーブル構造やデータを意識したバックアップ
バックアップ時のPostgreSQLと比べてリストア先のバージョンが新しい場合もリストアできる
オンラインバックアップ(ホットバックアップ)：データベース稼働中に実行するバックアップ
オフラインバックアップ(コールドバックアップ)：データベースを停止させて実行するバックアップ
・PITR
→データベース全体のバックアップに加えて、PostgreSQLの運用中にアーカイブとして記録された更新データを元にデータベースをリカバリする機能
ベースバックアップ：データベース全体のバックアップ
先行書き込みログ(WAL)：データベースに対して行われたすべての変更を記録する機構
WALアーカイブ：WALがいっぱいになったとき、別の場所へ保存すること
ロールフォワードリカバリ：リカバリ時ベースバックアップに対し、WALアーカイブに記録されたデータベースへの変更処理
WALファイルのデフォルトの大きさ：16MB
①設定：WALアーカイブの準備、postgresql.confの設定
→wal_level：WALに書き込まれる情報の度合いを指定。
・replica(デフォ)
・minimal
・logical
archive_mode：WALアーカイブを有効にするかどうかの指定。on, always, off(デフォ)
archive_command：WALファイルをWALアーカイブとしてコピーするためのシェルコマンドを指定
②ベースバックアップ：pg_start_backup()関数/pg_stop_backup()関数によるバックアップ。オンラインの物理バックアップ。
→スーパーユーザーで稼働中のPostgeSQLに接続して、pg_start_backup()関数→pg_stop_backup()関数
pg_start_backup('ラベル名')
pg_stop_backup()
pg_switch_wal()：新しいWALファイルへ強制移行する
・pg_basebackup
→クラスタのデータベースバックアップを取得
ストリーミングレプリケーション用のベースバックアップとして使用できる
pg_basebackup [オプション]
-P：コマンド実行時にバックアップ処理の進捗を表示
-X stream(デフォ)：WALファイルをバックアップ対象に含める
③リカバリ：
0.PostgreSQLを停止、データベースクラスタを別のディレクトリに移動
1.ベースバックアップのリストア
2.未アーカイブのWALファイルコピー
3.recovery.confの設定、PostgerSQLの起動
・recovery.conf
→リカバリに関する設定ファイル。$PGDATAディレクト下において起動するとリカバリがスタート。restore_commandパラメータにはWALアーカイブからWALファイルとしてコピーするシェルコマンドを指定。
リカバリが終了すると、recovery.confはrecovery.doneという名前のファイルにリネームされる。
オンラインバックアップでは、バックアップ中もデータベースに更新が行われる可能性があるため、バックアップ中に作成されたWALも自動でアーカイブされる。
リカバリポイントは指定することができる
※リカバリは必ずリハーサルをしてから実行する
・COPY文(SQL)、\copyコマンド(psql)の使い方
→テーブルのデータをファイルへコピー、ファイルのデータをテーブルへロードする
COPY文：サーバー側で動作。サーバー内で行き来する。
COPY テーブル名 TO {'絶対パスのファイル名' | STDOUT} [ [WITH] {オプション [, ...]}]
→コピー
COPY テーブル名 FROM {'絶対パスのファイル名' | STDOUT} [ [WITH] {オプション [, ...]}]
→ロード
・DELIMITER '区切り文字'：各行の列を区切る文字を指定する。デフォルトはタブ
・FORMAT csv：csv形式で入出力する場合に指定
・HEADER：ヘッダ行の指定。csv形式を指定した場合のみ有効
スーパーユーザーもしくは、ファイル読み込み可能なロール権限のみ
stdoutは標準出力
COPYコマンドでファイルを指定する場合はスーパーユーザ権限が必要
\copy：クライアント側で動作。クライアント側とサーバー側で行き来する。
スーパーユーザー権限不要
\copy テーブル名 to {ファイル名 | stdout} [with] [delimiter [as] '区切り文字'] [csv[header]]
\copy テーブル名 from {ファイル名 | stdout} [with] [delimiter [as] '区切り文字'] [csv[header]]
※ファイル名は''でくくらない
基本的な運用管理作業 【重要度：7】
PostgreSQLのトランザクション処理はMVCC(Multi Version Concurrency Control)を採用
共有ロックは起きないが、不要領域が発生する。
不要領域が多くなると「ディスク領域の圧迫」「I/O増加による性能低下」が発生する
そのためにVACUUMを使って、不要領域を更新していく。
・VACUUM、ANALYZEの目的と使い方
→VACUUM：テーブルの不要領域更新
vacuumdb(コマンドから)
VACUUM [FULL] [VERBOSE] [テーブル名](データベース接続後)
テーブル名省略時：すべてのテーブルが対象
VERBOSE=不要領域の詳細情報を取得
通常のVACUUM：テーブルに発生した不要領域を回収し、データ領域として使えるように再利用できるようにする。ただし、インデックスにも発生するため同様に回収が必要。VACUUMは指定したテーブルのインデックスも含めて回収する。また、VACUUM実行中でもSQL処理は問題なく可能。
VACUUM FULL：テーブルの内容を新しいファイルに書き換えて、不要領域を物理的にディスクから削除する。より多くの不要領域を回収できるが、排他ロックがかかるため他の処理はできない。大量の不要領域がある場合のみ使用する。FULLのときだけ、処理後のファイルサイズが小さくなる。
→ANALYZE：テーブルの統計情報の更新
ANALYZE [テーブル名]
テーブル名省略時；すべてのテーブルが対象
列に対しても実行可能
処理ははやい
VACUUM ANALYZE：VACUUMのあとにANALYZEが実行される。同時実行ではない。
vacuumdb -zまたは--analyze：VACUUM ANALYZEと同じ実行になる
vacuumdb -Zまたは--analyze-only：VACUUMを実行せず、ANALYZEだけを実行
vacuumdb -aまたは--all：すべてのデータベースを不要領域の対象とする。統計情報の収集は行わない
vacuumdb -fまたは--full：不要領域の回収を行い、回収した不要領域をOS上から削除する。排他ロックがかかる。
・自動バキュームの概念と動作
→VACUUMとANALYZEを自動で実行処理する機能。
データベース内の不要領域の割合が多くなったテーブルに対して、必要に応じて適宜実行される。
設定は「postgresql.conf」内でon(デフォ)かoffで設定。また、トランザクションIDの周回問題を回避するためでもある。
一時テーブルは対象外
バキューム対象に対して所有者権限が必要である
autovacuumパラメータの変更は、postgresql.confの再起動で変更可能。on(デフォ),off
・REINDEX
→インデックスを作り直し、インデックス内に生じてしまったから領域を解消するコマンド
・CLUSTER
→インデックスを使用して、テーブルのデータを物理的に並び替えるコマンド
・システム情報関数
→・version()関数：サーバーで稼働しているPostgreSQLのバージョンを確認する
※psqlで確認する場合に表示されるバージョンは「クライアント側のバージョン」
稼働しているバージョンを確認するには、version()関数を使用する
・current_database()関数：現在接続しているデータベースを確認する
・current_userとuser：SELECT文を実行したユーザのユーザ名を確認する。()はなし
・情報スキーマとシステムカタログ
→情報スキーマ(information schema)：データベース名のテーブルや列、ユーザーなどの様々なものについての定義情報が格納されている。
データベースオブジェクトに関する定義情報を含むスキーマ(データベース内で、テーブルなどのオブジェクトを格納している名前空間)
みれるのは現在接続中のテーブルのみ。PostgreSQL固有情報は含まれていないため、より詳細な情報を知りたい(データベース管理情報)ときは、「システムカタログ」をみる。
スキーマテーブルの情報もみれる。
・tables：テーブル情報
・columns：列情報
システムカタログ：データベースに関する内部情報を格納したテーブル。pg_catalogという名前のスキーマで定義。データベース管理情報がある。
・pg_roles：ユーザーの詳細情報。pg_authidカタログのビュー。パスワードはハッシュ化されている。データベースユーザー名はカタログ以外に情報スキーマのenable_rolesの情報からも得ることができる。
・pg_settings：パラメータ設定情報。パラメータの現在の設定は、setting列に入っている。変更や参照ができる。挿入や削除はできない。
nameカラム：パラメータ名、settingカラム：パラメータ値
internal：構築後の変更はできない、postmaster：サーバーの起動、再起動、signup：再読み込み、supseruser-backend：スーパーユーザーで新しいセッションを開始
backend：一般ユーザーで新しいセッションを開始、superuser：スーパーユーザーでSETコマンド実行、user：一般ユーザーでSETコマンド実行
・pg_database：データベース情報。クラスタ単位。
・pg_user：ユーザー名やIDなどデータベースクラスタ全体のユーザーに関する情報を格納している
・pg_authid：ロール名やロールの情報などデータベースクラスタ全体のロールに関する情報を格納している
・pg_indexes：インデックスを有するスキーマ名、インデックスを有するテーブル名などデータベース内のインデックスに関する情報を格納している
・pg_controldata：データベースクラスタの制御情報を取得。管理ユーザーのみ。-Dでクラスタ指定、省略$PGDATA
・pg_tables：テーブル情報
・pg_class：テーブルおよびテーブルに類する(列を持つ)オブジェクト(ビュー、シーケンス、マテリアライズド・ビューなど)に関する情報が格納
・pg_attribute：テーブルの列に関する情報
※システムカタログには「pg_」が先頭につく
・テーブル単位の権限（GRANT/REVOKE）
→GRANT：テーブル単位で権限の設定をする
GRANT {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | CONNECT | CREATE(スキーマ)}, [, ...] | ALL [PRIVILEGES]} ON {[TABLE] テーブル名 [], ...} TO {ユーザー名 | PUBLIC} [, ...] [WITH GRANT OPTION]
デフォルトではテーブルを作成したユーザーにすべての権限があり、他の人は参照できない。「WITH GRANT OPTION」で他のユーザーにも設定できるようになる。
※カラム名を指定するときは()でくくる。GRANT UPDATE(col1) ON tab1 TO usr1;
すべてのユーザーを指定するときはALLではなく、PUBLIC。
すべての権限を設定するときはALLが使える。
REVOKE：テーブルに設定されたアクセス権限を取り消す
REVOKE {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | CONNECT | CREATE}, [, ...] | ALL [PRIVILEGES]} ON {[TABLE] テーブル名 [], ...} TO {ユーザー名 | PUBLIC} [, ...] [WITH GRANT OPTION]
※列ごとにSELECT権限もできる
CONNECT=データベースへの接続許可
CREATE=スキーマ作成の許可
GRANT ロール名 TO ユーザ名;という付与の方法も可能。ロール名=ユーザー名と同等
GRANT...PUBLICをすると、あとで特定のユーザーをREVOKEした場合でもPUBLICが優先される？Ping-Tもう一度確認
→ドキュメント：例えば、PUBLICからSELECT権限を取り消すことは、必ずしも全てのロールがそのオブジェクトに対するSELECT権限を失うことを意味しません。 権限が直接許可されているロール、あるいは、別ロール経由で許可されているロールは、SELECT権限を持ち続けます。 同様にユーザからSELECTを取りあげても、PUBLICまたはほかのメンバとして属するロールがSELECT権限を持つ場合、SELECTの使用を拒否できません。
開発/SQL (32%)
---
SQL コマンド 【重要度： 13】
知らなかったSQL文
OFFSET
→LIMITと一緒に使い、指定した数分スキップする
EXISTS、NOT EXISTS
→サブクエリを使用してある条件に合致するものを検索したいときに使う
ANY
→INと同様の処理。異なるのはサブクエリで指定し、一つの条件しかできないこと
EXCEPT
→SELECTA EXCEPT [ALL] SELECTB
右側にはなく、左側にあるものを抽出する。ALLは重複を除去するかどうか。
INTERSECT
→SELECTA INTERSECT SELECTB
両方にあるもののみ抽出する。積集合
※UNION、INTERSECT、EXCEPTは連結することができるが、INTERSECTが一番優先して実行される
UNION / UNION ALL
→両方のテーブルを縦に結合する。UNION は重複をなくす、UNION ALLは重複も関係なくすべていれる。
power(数値、数値) / 数値^ ：累乗計算。power(4, 4)であれば、4の4乗を返す。べき乗や累乗が最優先に計算される。
数値! / !!数値：階乗を返す
now() + interval + '30 minutes'：30分後の時刻を取得
now();←カッコあり
current_date;←カッコなし
current_time, current_timestamp←通常はかっこなし、（）をつけるときは引数として精度を指定するとき
ILIKE：大文字小文字を区別しないで検索
lower(x) = 'abcd' / upper(x) = 'ABCD'：大文字小文字を区別しないで検索
|| での結合は、片方がNULLだったら結果はNULLになる
片方がNULLの場合に片方の値のみ出す場合は、
①SELECT concat(val1, val2) FROM test;
②SELECT coalesce(val1, ‘’) || coalesce(val2, ‘’) FROM test;
USING(id)：結合に使う列の名前が同じ時は、USING(列名)だけを指定することができる。USINGを指定して結合した場合は、同じキーのカラムはユニークで表示される。
natural join ：USING省略形。ID等を指定せず、2つのテーブルを結合させることができるが、同じ名前の列が複数ある場合、そのすべてを結合してしまう。
select * from emp natural join dept;
INSERT INTO テーブル名 (列名リスト) VALUES (値リスト);
INSERT INTO テーブル名 (列名リスト) SELECT文
※後ろにSELECT文でinsertするときはvaluesをいれるとエラーになる
SUM(2)のようにSUM（）の中に数値をいれると、テーブルの行数ごとに指定した数値が入り、その合算が取得される
テーブルデータ削除
DELETE FROM テーブル名 ((AS) テーブル別名) WHERE 条件式
・SELECT文とは違い、FROM句にカンマ区切りで複数のテーブルを並べることはできない
WHERE句で条件分岐したとき、NULLは除外される
FROM句で2つのテーブルを,でつなげるとCROSSJOINと同じ効果になる
=CROSS JOIN省略可能
min(カラム名)：最小値を返す、数値型、日付、時刻型、文字列型のカラムを指定可能
max(カラム名)：最大値を返す、数値型、日付、時刻型、文字列型のカラムを指定可能
データ型
整数型
・smallint：2バイト→16ビットの符号付き整数
・integer：4バイト→32ビットの符号付き整数。9桁までが安全
・bigint：8バイト→64ビットの符号付き整数。
任意整数型
・numeric（decimal）
→整数、小数両方扱える。最大1000桁までの精度。
浮動小数点型
・real：4バイト→最低6桁の精度
・double：8バイト→最低15桁の精度
連番型
・smallserial：2バイト
・serial：4バイト
・bigserial：8バイト
→連番指定にした場合は、自動的にシーケンスが作成され、not null制約が付与される
幾何データ型
・point：16バイト 座標点
・line：32バイト 無限の直線
・lseg：32バイト 有限の線分
・box：32バイト 矩形(四角形)
・path：16+16nバイト 閉回路
・polygon：40+16nバイト 多角形
・circle：24バイト 円
通貨型
・money：8バイト
文字型
・character/char：固定長
・varchar/character varying：上限値なし
・text：可変長
→PostgreSQLはバイト数指定のものはない。文字数指定。
※半角空白文字のみの場合は、指定文字数まで自動で切り詰められて格納される
上限文字数を設定しないVARCHAR・TEXT型に格納できるデータサイズは1GBまで
日付/時刻データ型
・timestamp
・date
・time
・interval
論理型
・boolean
バイナリ列データ型
バイトの連続からなるテキスト以外のデータ。BYTEA型。1または4バイトと実データの長さ。可変長のバイナリ列を格納。
OID
・識別IDを番号(一意)、OIDを使ってオブジェクト情報の管理をしている。システムテーブルやシステムビューから特定のオブジェクトの情報を取得したいときに使う。
ラージオブジェクト
・巨大なバイナリ、テキストデータを格納できる機能
・OIDを使用してラージオブジェクトデータを紐付ける
配列
・配列型使用可能。すべてのデータ型に使用することができる。
create table array_table c1 int[];
insert into array_table values ('{1,2,3');
select c1[1:2] from array_table;
select * from array_table where c1[2]=3;←c1の2番目が3の値
update array_table set c1[1]=5;
NULL
→IS NULL、IS NOT NULL
NULLが入ってる列をソートしたい場合、
NULLを最初に持ってくる ORDER BY 列名 NULL FIRST
NULLを最後に持ってくる ORDER BY 列名 NULL LAST
CAST
→データ::変換後のデータの型
c1::text || '-' || c2::text
cast('123' as int)
制約
→テーブル列に対して特定の条件を満たす値だけを許可したい場合に使用する
CREATE TABLEもしくはALTER TABLE時に使用
・主キー制約：主キー作成時に使用
CREATE TABLE テーブル名(列名 データ型 PRIMARY KEY);
ALTER TABLE テーブル名 ADD PRIMARY KEY (列名);
ALTER TABLE テーブル名 ADD CONSTRAINT 主キー名 PRIMARY KEY (列名);
複数指定可能
主キー名を指定しない場合は「テーブル名_pkey」という名前になる
主キーの削除
ALTER TABLE テーブル名 DROP CONSTRAINT 主キー名;
・ユニーク制約：一意の列にしたいときに使用
CREATE TABLE テーブル名(列名 データ型 UNIQUE);
ALTER TABLE テーブル名 ADD UNIQUE(列名);
ALTER TABLE テーブル名 ADD CONSTRAINT ユニークキー名 UNIQUE(列名);
複数指定可能
ユニークキー名を指定しない場合、「テーブル名_列名_key」という名前になる
ユニークキーの削除
ALTER TABLE テーブル名 DROP CONSTRAINT ユニークキー名;
・NOT NULL制約：NULLを含みたくないときに使用
CREATE TABLE テーブル名(列名 データ型 NOT NULL);
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;
NOT NULL制約の削除
ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP NOT NULL;
・外部キー制約：別テーブルのキーを参照したい場合に使用
CREATE TABLE テーブル名(列名 データ型 REFERENCES 参照先テーブル名 (列名));
CREATE TABLE テーブル名(列名 データ型, FOREIGN KEY(列名) REFERENCES 参照先テーブル名(列名));
ALTER TABLE テーブル名 ADD CONSTRAINT 外部キー名 FOREIGN KEY(列名) REFERENCES 参照先テーブル名(列名);
外部キー名を指定しないときは、「テーブル名_列名_fkey」という名前になる
参照先テーブルの列は、主キーかユニークである必要がある
外部キー制約の削除
ALTER TABLE テーブル名 DROP CONSTRAINT 外部キー名;
※外部キーで参照されているテーブルの列を削除/更新するとデフォルトではエラーになる。
連動させるようにするには以下のように記載する
・CREATE TABLE テーブル名(列名 データ型 REFERENCES 参照先テーブル名(列名) [ON DELETE CASCADE] [ON UPDATE CASCADE]);
・ALTER TABLE テーブル名 ADD CONSTRAINT 外部キー名 FOREIGN KEY(列名) REFERENCES 参照先テーブル名(列名) [ON DELETE CASCADE] [ON UPDATE CASCADE];
・チェック制約：ある列に対して特定の条件を満たす値のみ許可したいときに使用
CREATE TABLE テーブル名(列名 データ型 CHECK(条件式));
ALTER TABLE テーブル名 ADD CONSTRAINT チェック制約名 CHECK(条件式);
ANDでつなげることで複数指定も可能
チェック制約名を指定しない場合「テーブル名_列名_check」という名前になる
チェック制約の削除
ALTER TABLE テーブル名 DROP CONSTRAINT チェック制約名;
・ドメイン制約：チェック制約をもったデータ型をカスタムに作成。テーブルを作成するときにドメイン制約でつくったデータ型にすることができる。
CREATE DOMEIN ドメイン名 AS データ型 [NULL | NOT NULL | CHECK(条件式)];
ドメイン制約の削除
DROP DOMAIN ドメイン名;
DROP DOMAIN ドメイン名 CASCADE;
→ドメイン制約が適用されている列そのものが削除される。
変更
ALTER DOMAIN ドメイン名;
その他テーブル定義変更例
・テーブル名の変更：ALTER TABLE テーブル名 REMAME TO 新しいテーブル名;
・テーブルの所有者変更：ALTER TABLE テーブル名 OWNER TO 新しい所有者となるユーザー名;
・列名の変更：ALTER TABLE テーブル名 RENAME COLUMN 列名 TO 新しい列名;
・列の追加：ALTER TABLE テーブル名 ADD COLUMN 追加する列名 データ型;
・列の削除：ALTER TABLE テーブル名 DROP COLUMN 削除する列名;
パーティション
→テーブルを範囲で区切る
CREATE TABLE 親テーブル名(列 データ型) PARTITION BY RANGE (列名):
CREATE TABLE パーティションテーブル名 (列名 データ型) PARTITION OF 親テーブル名 FOR VALUES 条件;
パーティションテーブルの削除
DROP TABLE パーティションテーブル名
あとから追加したい場合
ALTER TABLE 親テーブル名 ATTACH PARTITION パーティションテーブル名 FOR VALUES 条件;
→親テーブルに組み込む
ALTER TABLE 親テーブル名 DETECH PARTITION パーティションテーブル名;
→親テーブルから切り離す
継承
・CREATE TABLE partition1 () INHERITS (parent)
継承すると、親テーブルをSELECTしたときに子テーブルの内容もみれる
利点
・問い合わせの性能を大幅に向上
・頻繁にアクセスする行と、そうでない行を、別のディスク領域に配置
・DROP TABLEを実行して、大量データの一括削除を高速化
注意点
・パーティションは定義されていなければ実行できない
・パーティション毎にデータの移動はできない
・インデックスやトリガーは各パーティションごとに作成しなければならない
宣言パーティションの種類
・リスト・パーティショニング：値によって分割する。値によってアクセス範囲を絞りたい場合に使用
CREATE TABLE 親テーブル名 (列 データ型) PARTITION BY LIST (キーとなるカラム名);
CREATE TABLE パーティションテーブル名(列名 データ型) PARTITION OF 親テーブル名 FOR VALUES IN (分割するカラムの値);
・レンジ・パーティショニング：値の範囲によって分割する。ある値の古いデータから削除していきたい等の場合に使用。通常のdateをキーにするもの。
CREATE TABLE 親テーブル名(列 データ型) PARTITION BY RANGE (キーとなるカラム名):
CREATE TABLE パーティションテーブル名 (列名 データ型) PARTITION OF 親テーブル名 FOR VALUES 条件;
・ハッシュ・パーティショニング：ハッシュ値を使用して、ほぼ均等にデータを分割する方法。INSERT処理の精度が高く、INSERT先を分散することで競合を防ぐ場合に使用
CREATE TABLE 親テーブル名 (列 データ型) PARTITION BY HASH (キーとなるカラム名);
CREATE TABLE パーティションテーブル名(列名 データ型) PARTITION OF 親テーブル名 FOR VALUES WITH (MODULUS 分割する数, REMAINDER 分割する数より小さな整数値);
シーケンス
自動で連番を払い出してくれる。ユニークIDなどを払い出す際に多用
CREATE SEQUENCE シーケンス名 [オプション];
・INCREMENT 増加値：シーケンスの増加値を設定。デフォルトは1。マイナス値の設定も可能。
・START 開始値：シーケンスの開始値を設定。デフォルトは1。マイナス値の設定も可能。
・[NO] CYCLE：シーケンスが最大値まで達した場合の振る舞いを指定。デフォルトはNO CYCLE。
・CACHE キャッシュ数：メモリに格納できるシーケンス番号の量を指定。CACHE 5 NO CYCLE：メモリに格納できるシーケンス番号の量=5、限界値の場合はノーエラー。
・MINVALUE 最小値：シーケンスの最小値を指定。
・MAXVALUE 最大値：シーケンスの最大値を指定。
変更
ALTER SEQUENCE シーケンス名 オプション：内容変更、所有者変更、名称変更
削除
DROP SEQUENCE シーケンス名;
currval('')：今のシーケンスを表示
nextval('')：次のシーケンスを表示
setval('')：シーケンスの値を設定
・シーケンスはテーブルとして実装されるので、内容をみるときは、select * from テーブル名(シーケンス名)で取得することができる
・トランザクション内でシーケンスが使われた場合、ROLLBACKされても、シーケンスの中身はリセットされない
ビュー
select文の結果
CREATE VIEW ビュー名 [(列名)] AS SELECT文：
※ビューの作成時には、ビューの元となるテーブルに対してSELECT権限が必要。ビュー元テーブルのCREATE権限はいらない。
ビューを参照する場合ビューに対してのSELECT権限が必要
削除時
DROP VIEW ビュー名
・既存のテーブルと同じ名前のビューは作れない
・ビューの更新にはトリガー(trigger)あるいはルール(rule)を定義する必要があったが、バージョン9.3以降では、(ビューの定義によっては)トリガーやルールが定義されていないくても更新が可能になった。
・CREATE OR REPLACE VIEWコマンドでビューの定義を変更するとき、ビューに列を追加することはできるが、減らすことはできない。
変更
ALTER VIEW
更新可能ビュー
9.3移行、ルール定義なしでビューが更新できるようになった。
条件
・元となるSELECT文のFROM句に複数のテーブルが記述されているビューは更新できない。
・元となるSELECT文がSUMなどの集約関数を使っているビューは更新できない。
・元となるSELECT文が複数のSELECT文のUNIONになっているビューは更新できない。
・ビューにINSERTできた行が、SELECTで参照できるとは限らない。
=そのテーブル内でまかないきれてないビューは更新できないということ。
インデックス
辞書の役割を担い、索引と呼ばれる。
大きなデータを処理する際には、インデックスを定義しておくことで処理が高速される。
自動管理される。
※ただし、必ずしも性能が向上するとは限らない。
また、テーブルとインデックス２つの更新をしないといけなくなるため、更新処理性能は低下することもある。
・B-Tree：一般的なインデックス。スカラー値に対する一致や不等号の検索に用いる。デフォルト。
・GiST：空間情報の検索に用いられるインデックス。
・GIN：全文検索に用いられる転置インデックス。
・ハッシュ：値の一致検索のみをサポートするインデックス。
作成
CREATE INDEX インデックス名 ON テーブル名 [USING インデックス種別]( 列名);
一意のインデックスの作成：CREATE UNIQUE INDEX
※PRIMARY KEY、UNIQUEの制約がある列については自動的に作成される
削除
DROP INDEX インデックス名
変更
ALTER INDEX インデックス名
※変更できるのは名称のみ
マルチカラムインデックス：複数列を対象にしたインデックス。検索条件で多用される列の組み合わせをインデックス化し、検索性能を向上させる。
※使用できる型は、B-Tree、Gist、GIN
関数インデックス/式インデックス：関数や式の結果を検索条件とし、検索性能を向上させる。
部分インデックス：テーブルの特定の範囲のデータのみにインデックスを作成する
トリガ
特定のテーブルに対して挿入/更新/削除が行われた場合に、規定のユーザー関数を発動させる機能
※SELECT文はトリガ定義ができない。
発動タイミング
・行ごと(行トリガ)、SQLごと(文トリガ)のいずれかを指定
・UPDATE/INSERT/DELETE/TRUNCATEの種別を指定
・更新対象の列を指定
・実際に更新されたかどうかを指定
・更新の前後を指定
作成
CREATE TRIGGER トリガ名 {BEFORE | AFTER} {UPDATE | INSERT | DELETE | TRUNCATE} ON テーブル名 [FOR [EACH] {ROW | STATEMENT}] EXECUTE PROCEDURE 関数名;
・更新種別は複数選択可能。OR
・UPDATE のみ UPDATE OF 列名で特定の列の更新とすることも可能
・特定の列が更新されたかどうかの判断「OLD」「NEW」で判断。UPDATEは両方。INSERTはNEWのみ、DELETEはOLDのみ。
・行ごとはSQLごとかは、「ROW」「STATEMENT」で判断
・SQL言語でのトリガーはまだ作成できない
・ビューにINSTEAD OFトリガーを定義することで、ビューを更新することもできる
・トリガーが実行される順序はトリガー名の昇順
文レベルトリガ：SQL文の実行に対して1回だけ実行
行レベルトリガ：更新の対象となった各行について個別に実行される
4通りのトリガの順番は、BEFORE文→BEFORE行→AFTER行→AFTER文
削除
DROP TRIGGER トリガ名 ON テーブル名;
更新
ALTER TRIGGER トリガ名 ON テーブル名 RENAME TO 新しいトリガ名
※変更は名称のみ
ルール
ある条件のSQLが発行された場合、それを別のSQLで置き換えたり、別の処理を付け加えたりしたい場合に使用。
作成
CREATE RULE ルール名 AS ON イベント名 TO テーブル名 DO [INSTEAD | ALSO] SQL文;
イベント名=INSERT|SELECT|UPDATE|DELETE。INSERT|UPDATEを指定した場合、NEWがSQL文で使える。
DO INSTEAD：本来のSQLの処理に置き換えたい場合
DO ALSO：追加で実施した処理がある場合
削除
DROP RULE ルール名 ON テーブル名;
変更
ALTER RULE
※名称の変更しかできない
ルール作成時にREPLACEオプションを指定すると、既存のルールを置き換えることができる
ルールを使用することで、通常は更新条件を満たさないビューに対して、更新処理を行うことができる
実行できるのは、テーブルかビューの所有者であればできる
スキーマ
テーブルや関数などのオブジェクトが含まれ、データベースの中に複数のスキーマが存在する。
デフォはpublic。
スキーマの検索パス設定をした場合、最初から優先的に取得される
SET search_path TO "$user",mary,public;
作成
CREATE SCHEMA スキーマ名 [AUTHORIZATION データベースユーザー名];
CREATE SCHEMA AUTHORIZATION データベースユーザー名;
AUTHORIZATION=スキーマの所有者を指定。省略した場合、データベースユーザ名。
ALTER SCHEMA：スキーマの名前や所有者を変更できる
・あるスキーマにテーブルを新規に作成するには、そのスキーマに対するCREATE権限が必要
・スキーマを新規に作成するには、データベースに対するCREATE権限が必要
削除
DROP SCHEMA [-CASCADE]
・CASCADEをつけるとオブジェクトの中身を削除してスキーマを消す。スキーマの中にオブジェクトがある中で、CASCADEをつけないとエラーになる。
特定のインデックスでテーブルを作成したいとき
1.my_schema.test1(c1 int, c2 int)
2.SET search_path to スキーマ名;
関数とプロシージャ(ストアドプロシージャ)
関数とプロシージャの違い
・関数は戻り値を定義できるが、プロシージャはできない(create functionでも戻り値なし定義はできる(void型関数))
・関数はselectで呼び出すが、プロシージャはcallで呼び出す
・関数は定義内でトランザクションのCOMMITやROLLBACKができないが、プロシージャはできる
関数
CREATE [OR REPLACE] FUNCTION 関数名(引数) RETURNS [SETOF] 戻り値 AS $$
関数の記述
$$ LANGUAGE 使用言語;
※$$は''でも問題ない。$$を使っている理由は、関数の記述の中で''を使う機会が多いので、わかりずらくならないために$$を推奨している
プロシージャ
CREATE [OR REPLACE] PROCEDURE プロシージャ名(引数) AS $$
関数の記述
$$ LANGUAGE 使用言語;
変更
ALTER PROCEDURE
削除
DROP PROCEDURE
・OR REPLACE=既存の関数を上書き
・SETOF=複数の戻り値を指定したい場合
・引数=データ型か変数名とデータ型のセットを記述
関数の構造
PL/pgsqlによる関数の作成
DECLARE
変数の宣言(データ型以外にtable_name%ROWTYPE(指定のテーブルと同じ構造を表す行型)やrecord(行構造の代入))
BIGIN
関数の処理記述
END;
※DECLAREとBIGINのあとは;はつけてはいけない
変数名 [CONSTANT] データ型 [NOT NULL] [{DEFAULT | :=} 値など];
・DEFAULTがない場合はNULL
・CONSTANT=変数の代入を禁止する
カーソル
SELECT文の結果を、LIMITを使用せずに任意の位置から必要な件数分取り出したり、取り出した行の更新・削除を行いたい場合に使用する
作成：DECLARE CURSOR
DECLARE カーソル名 [オプション1] CURSOR [オプション2] FOR データを返す問い合わせ;
オプション1
・INSENSITIVE(デフォ)：カーソルから取得したデータが、取得元テーブルの更新に影響をうけないことを示す
・SCROLL(デフォ)：データの順方向・逆方向のどちらにもカーソルを動かせるように指定する
・NO SCROLL：データの順方向にカーソルを動かせるように指定する。逆方向には動かせない
オプション2
・WITH HOLD：トランザクションがコミットされたあともカーソルがしようできるようにする
・WITHOUT HOLD(でふぉ)：トランザクション内でのみカーソルを使用できるようにする
MOVE
カーソルの移動のみを行うコマンド
MOVE [オプション [FROM]] カーソル名;
FORWORD：順移動
FETCH
カーソルの移動と同時にデータを取得するコマンド
FETCH [オプション [FROM]] カーソル名;
BACKWARD ALL：逆順、すべて
オプション省略の場合はNEXT
ALL＝次移行の全データを取得
PRIOR=前の行のデータを取得
プリペアド文
SQL書き換え機能。機能的には関数と同じような処理ができる。
関数、ルール、プリペアド文で書き換えができる。
プリペアド文を使用すると指定した問い合わせの書き換え・構文の解析・実行計画の作成が一度実行するだけで済むため、性能の向上が図れる。
作成：PREPARE
PREPARE プリペアド文の名称 [(引数のデータ型[, 引数のデータ型])] as 問い合わせ;
問い合わせ=SELECT, INSERT, UPDATE, DALETE, VALUES
実行：EXECUTE
EXECUTE プリペアド文の名称[(引数[, 引数])];
削除：DEALLOCATE
DEALLOCATE [PREPARE] {プリペアド文の名称[引数][, 引数] | ALL};
テーブルスペース
データベースのデータは物理的にテーブルスペースという場所に格納される
postgreSQLではユーザーが新たにテーブルスペースを用意することもでき、そこに性能の良いストレージをいれたり、分散させたりして効率性を高めた運用も可能となる。
データベースオブジェクト作成時、変更時に格納するテーブルスペースを指定することができる
配置要素
・DATABASE
・TABLE
・INDEX
作成
CREATE TABLESPACE テーブルスペース名 LOCATION 'パス名';
削除
DROP TABLESPACE テーブルスペース名;
変更
ALTER TABLESPACE テーブルスペース名;
マテリアライズドビュー
実体をもつビュー
即時性を求めないようなデータで結果取得に時間がかかるようなビューが必要なときに効果的
=高速
インデックスの作成が可能
データの参照のみ可能
普通のビューは条件をクリアしたビューであれば、更新挿入削除ができる
デメリット
別ディスクにデータを保存する＝容量を気にする必要がある
作成
CREATE MATERIALIZED VIEW マテリアライズドビュー名 AS SELECT文;
更新
REFRESH MATERIALIZED VIEW マテリアライズドビュー名;
※普通のビュー：元テーブルが更新されたら自動的に更新される
マテリアライズ：手動更新
削除
DROP MATERIALIZED VIEW マテリアライズドビュー名;
オプション
・IF NOT EXISTS：すでに同じ名前のビューがある場合もエラーにならない
・IF EXISTS：指定した名前のビューがない場合もエラーにならない
・WITH DATA：ビュー作成時にデータをいれる
・WITH NO DATA：ビュー作成時にデータをいれない(デフォ)
・CASCADE：ビューに依存するオブジェクトも削除する
・RESTTICT：ビューに依存するオブジェクトがある場合は削除しない(デフォ)
組み込み関数 【重要度：2】
floor()：引数の値を超えない最大の整数を返す
floor(10.2)→10
ceil()：引数の値を超える最小の整数を返す
floor(10.2)→11
round()：小数点部分の四捨五入
round(10.5)→11
trunc()：切り捨て
trunc(10.5)→10
@ (数値) / abs(数値)：絶対値を取得
random()：0以上1未満のランダムな値を返す
similar to '条件'：正規表現を使ったパターンマッチングができる。全体に対してパターン一致検索なので、%を付与する必要がある。
・POSIX正規表現
~：文字列のパターンマッチングを行う演算子。文字列含む一致のとき。一部に対してパターン一致検索するので、%はいらない。そのまま記載。
文字列 ~ 正規表現：正規表現に一致する場合に真となる。大文字小文字が区別される。どっちかしか読み取れない。
文字列 ~*正規表現：正規表現に一致する場合に真となる。大文字小文字が区別されない。どっちも読み取れる。
文字列 !~正規表現：正規表現に一致しない場合に真となる。大文字小文字が区別される。
文字列 !~*正規表現：正規表現に一致しない場合に真となる。大文字小文字が区別されない。
substring：文字列抽出
substring (文字列 from スタート値 for 取得する数)
substring(str1 from 1 for 1) →str1の1から1文字
substring(str1 for 1) →str1の最初から1文字
substring(str1, 1, 1) →str1の1から1文字
substring(str1, 1) →str1の1からすべての文字
substring(str1 from 1) →str1の1からすべての文字
position：文字列番号抽出
position(文字列 IN 列名)
octet_length()：文字列のバイト数を取得。octet_length(文字列)
char_length()/length()：文字列の数を取得
trim()：指定した文字を削除した文字列を取得。空白削除とかで使う。
trim ([leading | trailing | both] [文字列] from [文字列])
lpad()/rpad()：指定した文字を埋め込む
lpad(文字列、文字数、[文字])：先頭から文字を埋め込む
rpad(文字列、文字数、[文字])：末尾から文字を埋め込む
now()/current_timestamp：トランザクション開始時のタイムスタンプを取得
statement_timestamp()：SQL開始時のタイムスタンプを取得
clock_timestamp()：関数が実行された時点の日付/時刻を取得
age()：2つのtimestamp型の引数をとり、第一引数から第2引数の減算を取得
timestamp '日付または日時'、'日付または日時'::timestamp
extract()/date_part()：タイムスタンプ型の部分フィールドを返す
extract(field from timestamp) / extract(field from interval)
date_part('field', timestamp) / date_part('filed', interval)
date_trunc('filed', timestamp)：指定した粒度で日付/時刻情報を取得する
トランザクションの概念 【重要度：1】
トランザクション：複数のSQL処理をBIGINやCOMMITなどのコマンドで囲み、グループ化した処理
ACID特性：
Atomicity(原子性)：トランザクションは実行が完了する、あるいは全く実行されない
Consistency(整合性)：トランザクションの開始と終了時には、データベースは整合性を保った状態となっている
Isolation(分離性)：トランザクションは別のトランザクションによる処理の影響をうけない
Durability(持続性)：トランザクションにより変更されたデータは確実に保持される
トランザクションの構文
BIGIN/START TRANSACTION：トランザクションの開始
COMMIT...END/END...TRANSACTION ：処理の確定
ROLLBACK/ABORT：処理を開始する前の状態に戻す
トランザクション中にエラーが発生した場合は、その時点でABORT扱いとなる。
※psqlのデフォルトではauto commitがオンになっているため、BIGINを発行しないとトランザクションが処理される。
SAVEPOINT：トランザクション中に部分的にロールバックしたいときに使用。
SAVEPOINT セーブポイント名;
ROLLBACK TO 戻る時点のセーブポイント名;
削除：RELEASE SAVEPOINT セーブポイント名;
一つのトランザクションの中で同じ名称のSAVEPOINTを設定できる。同じ名称の場合は、先に設定したSAVEPOINTが上書きされる。(最新のものが設定される)。最新のものを削除した場合は、一個前の新しいものが設定される。
BIGIN→COMMIT/ROLLBACKはワンセット
※自動コミットがONになっている場合、BIGIN...COMMITが定義されていない箇所は、SQLが記載された時点でCOMMITされる。
トランザクション分離レベル(リードコミッティド、リピータブルリード、シリアライザブル)
ダーティリード：自身のトランザクションから別のトランザクションの未コミットな状態の挿入/更新/削除結果がみえてしまうこと
ファジーリード：自身のトランザクションから別のトランザクションのコミットされた更新/削除結果が見えてしまうこと。
ファントムリード：自身のトランザクションから別のトランザクションのコミットされた挿入結果が見えてしまうこと
直列化異常：複数のトランザクションのコミット結果がトランザクションを1つずつ重ならないように実行した場合と比較して、どのような順序を仮定しても違う結果になり、一貫性のない状態になってしまうこと。
Read uncommited：上記4つすべて起こる。PostgreSQLでは、Read uncommitedを設定しても、Read commitedと同じふるまいになる。
Read commited：ファジーリード、ファントムリード、直列化異常が起こる。デフォルト。
Repeatable read：ファントムリード、直列化異常が起こる。トランザクション内でSELECTしたタイミングによって、データの内容が異なる時がある。
serializable：どれもおこらない
設定：default_transaction_isolationで設定する、もしくはSET TRANSACTION文での指定
SET default_transaction_isolation to '分離レベル';(セッション中)
SET transaction_isolation to '分離レベル';(トランザクション中)
BEGIN ISOLATION LEVEL 分離レベル;(トランザクション中)
START TRANSACTION ISOLATION LEVEL 分離レベル;(トランザクション中)
分離レベルを設定するのは、トランザクション開始のBEGIN(あるいはSTART TRANSACTION)コマンドのオプションで指定するか、
あるいはBEGINの直後にSET TRANSACTIONコマンドで指定
確認：SHOWコマンド
SHOW default_transaction_isolation;
SHOW transaction_isolation;
トランザクションの分割処理はできない
トランザクション内でcurrent_timestamp;とかを連続してやると同じ値になる
ロック
更新処理時の矛盾を避けるための排他処理として利用される。
行ロック：ある行に対して取得されるロック、排他ロックと共有ロックがある。共有ロック同士は衝突しない、共有ロックと排他ロック、排他ロック同士が衝突する。
排他ロックは、UPDATEやDELETEといったレコードの更新や削除処理時に自動的に取得される
また、FOR UPDATE(排他ロック)とFOR SHARE(共有ロック)を使って取得することもできる
SELECT 列名 FROM テーブル名 WHERE 条件 FOR UPDATE;
SELECT 列名 FROM テーブル名 WHERE 条件 FOR SHARE;
デフォのトランザクションレベルはREAD COMMITED
テーブルロック：テーブル単位での排他/共有ロックを明示的に取得する場合に使用
基本的にロックの粒度は小さいほど同時実行性能は向上するため、テーブルロックはあまりしないほうがいい。
LOCK TABLE テーブル名 [IN ロックモード];
ロックモード
・EXCUSIVE MODE：ロック対象のテーブルへのSELECTのみ許可。
・ACCESS EXCLUSIVE MODE：すべての処理をブロック。デフォルト
ALTER TABLEはテーブルの排他ロックが自動的に実行される
デッドロック：ある2つの処理がお互いの処理の終了を待っており、処理を進めることができない状態。Postgesqlでは、デッドロックを検知したトランザクションをアボートし、自動的に回復する
