とりあえずリファレンスを見れば何でも載っているけど、特殊なテーブルや知ってると便利かもしれない事柄についてメモ。
|
SELECT文で テーブルの項目を使わない場合は、FROM句（テーブル名）を省略できる。
（OracleならDUALテーブルを使うようなケース）
select 'abc';
UPDATEでも、「Cannot insert a duplicate key into unique index tbl_xxxx_pkey」というエラーが出る可能性がある。
キーにaaa,bbbという値が入っている２つのレコードに対し、
UPDATE tbl_xxxx SET key='aaa' WHERE key = 'bbb';
という更新を行うと、aaaというキーのデータが既に在るのでこのエラーになる。ただし、
UPDATE tbl_xxxx SET key='bbb' WHERE key = 'bbb'; ←キーの更新だが、結果的に変わらないので問題無い
は大丈夫。
権限の付与はGRANT・削除はREVOKEで行う。
権限の照会はpsqlの\zで行う。「ユーザー名=arwdRxt」といった形で表示される。
| a | append（INSERT） | INSERTの実行権限 |
| r | read（SELECT） | SELECTの実行権限 |
| w | write（UPDATE） | UPDATEの実行権限 |
| d | DELETE | DELETEの実行権限 |
| R | RULE | ルールの作成権限 |
| x | REFERENCES | 外部キー制約 |
| t | TRIGGER | トリガーの作成権限 |
CREATE USER（createuser）で作成する。変更はALTER USER。削除はDROP USER（dropuser）。
照会は以下の様なSQLで行う。
select * from pg_shadow; ←権限がある人のみ実行可能。パスワードも見られる。
pg_shadowで表示されるパスワードは、MD5形式の時は暗号化されている。文字列の先頭がmd5になっていたような…。
select * from pg_user; ←権限が無くても実行可能。パスワードは見られない。
select current_user, session_user; ←現在のユーザーIDを表示する関数を使用
パスワードの設定は、以下の様にして行う。
ALTER USER ユーザー名 PASSWORD 'パスワード';
CREATE GROUPで作成する。変更はALTER GROUP。削除はDROP GROUP。
照会は以下の様なSQLで行う。
select * from pg_group;
groname | grosysid | grolist
--------------+----------+---------------
group_abcdef | 100 | {107,108,109}
grolistの数値は、pg_userビューのusesysid項目の値。
トリガーはINSERT・UPDATE・DELETEの実行前後に呼ばれるもの。呼ばれた際にどういう動きをするかは関数で実装する。
例えば、外部キー制約はトリガーで実装されている。
CREATE TRIGGERで作成する。削除はDROP TRIGGER。変更は 削除＋作成。
テーブルに付いているトリガーはpsqlの\dで確認できる。
トリガーの一覧は以下の様なSQLで照会できる。
select * from pg_trigger;
トリガーに割り当てられている関数の名称は、以下の様なSQLで知ることができる。
select tgname,proname from pg_trigger t, pg_proc f where t.tgfoid = f.oid and tgname = 'トリガー名';
CREATE FUNCTIONで作成する。変更はCREATE OR REPLACE FUNCTION。削除はDROP FUNCTION。
関数にpgsqlを使いたい場合は、createlangでpgsqlを使えるように指定しなければならない。
作成した関数の中身は以下のSQLで照会できる。
SELECT prosrc FROM pg_proc WHERE proname = '関数名';
PostgreSQLには、文字列操作や型変換・日付時刻関連（現在時刻の取得や変換・演算）等の様々な関数が用意されている。
| 豆知識 | 例 | 備考 |
|---|---|---|
| timestamp型に 単純に数値を足すと、時刻部分が消える。 | select current_timestamp + 3; ３日後の日付が取得される |
|
| psqlの\encodingに該当する関数 | # select pg_client_encoding(); pg_client_encoding -------------------- UNICODE (1 row) |
# \encoding UNICODE |
関数には、末尾に括弧を付ける必要が有るものと無いものがあるらしい。
BEGINでトランザクションを開始し、COMMIT又はROLLBACKで終了。
「FOR UPDATE」を指定してロックをかけたSELECTの場合も、COMMIT又はROLLBACKするまでロックしたままになる。
トランザクションの中で１回でもSQLエラーが発生すると、COMMITは出来なくなる。
トランザクション内の時刻は全て同じになる（COMMIT時の時刻？）。
↓２つのSQLでは連続して実行しても異なる時刻になる update A set DATE1 = current_timestamp; update A set DATE2 = current_timestamp;
↓同じ時刻になる BEGIN; update A set DATE1 = current_timestamp; update A set DATE2 = current_timestamp; COMMIT;
SQLの実行にどれくらいのコスト（時間）がかかるかをEXPLAINで表示できる。
EXPLAIN SQL文 ; EXPLAIN ANALYZE SQL文 ;
ANALYZEを付けない場合、表示される時間は参考程度。costが小さいほど実行が早い。
ANALYZEを付けると、実際にSQLを実行して実時間を表示する。UPDATE文等だと本当に更新してしまうので注意。
SQLの実行計画は、同じDB構成であっても中に入っているデータによって異なるし、VACUUM ANALYZE（統計情報の更新）をかけているかどうかでも変わってくる。SQLの実行が遅いと思ったら、まずANALYZEをかけよう。するとしないじゃ大違いだから！
SQL文の最適化についてはこちらを参照。
UPDATEやDELETEによって更新を行うと、見た目はデータが無くなったように見えてもPostgreSQL内部では削除フラグを立てているだけで、データは消えていない。（単なる）VACUUM（vacuumdb）を行うことによって、そのデータの領域が再利用されるようになる。
ただし、INDEX領域はVACUUMだけでは再利用されないらしい。
また、データの更新によって拡張したデータのディスク領域はVACUUMで減ることは無い。VACUUM FULLを行うと減らせる。