PostgreSQL on 
MacOS with pgAdmin4 テストメモ
TL; DR
- ただのメモです。
- psqlコマンドも記載します
実行環境
- OS:Max OS El Capitan 10.11.6
- PostgreSQL 9.6.1
- pgAdmin 4.1.1
※ サクッと試したかったので、仮想OSではなく、Mac環境のPostgreSQL DBでテストしました。
pgAdmin4
やはり時代は、タブを使ったインターフェースですよね
SQLタブ
テーブル作成時のCREATE文が確認できるため、カラムのデータ型や制約を確認できる。
pgAdmin3では、デフォルトのビューで見えていたのですが、pgAdmin4ではタブを切り替える必要があります。なんか設定ないかな。。。
QueryTool
なんか名前と場所が変わって見つけづらかったSQLビルダーです。
マークをたどっていけば、SQLを実行できます。F5でクエリ実行は健在です。
ctrl+space
入力補完が出る！！！入力補完が出る！！！
タブは2文字にしたい
- メニューバーから「File」をクリック
- ドロップダウンメニューから「Preference」
- SQL EditorのOptionでTab Sizeを変更
- ドロップダウンメニューから「Preference」
クエリツールのExplain
実行するクエリの分析をしてくれます.
コストも表示されていたので、なかなか使えそうです。
- クエリツールの実行ボタン横のドロップダウンメニュー
- Explainを実行
- 右下のパネルのExplainタブを選択
- Explainを実行
クエリツールの右下のパネルの横スクロールバーが表示されない
自分の環境では、横スクロールはできるがバーは表示されませんでした。
メニューバーのメニューはクリックで開閉
見た目がWebサイトっぽいけど、オンマウスでドロップダウンメニューを開くってことはできないみたいです。ドロップダウンメニューを開いたけど、他のパネルに移動した時にメニューが開いたままになる。
SELECT文の結果をコピーできない
環境問題だと思いますが、Rowを選択して、コピーのショートカットやコピーアイコンを押しても結果がコピーできませんでした。CSVに出力するしかない。
psqlコマンド
- psql db_name
データベースに接続
postgres-# のように、プロンプトがdb_nameになります。
オプション
詳細はPostgreSQL 9.5.4文書 PostgreSQLクライアントアプリケーション psql参照
- -l
データベースの一覧を表示させる - -a
読み込んだ行を全て出力
つまり、実行したSQLが標準出力される。 - -f filename
ファイルに書き込んだSQLを実行。\iと同様。
「psql < filename」との違いは公式参照。 - -h hostname
SQLサーバのホスト名を指定 - -L filename
全ての出力を標準出力と指定ファイルに出力する。Logfileです。
実行したSQLもファイルに含まれてしまうので、データを加工したい時には向かない。
-oの出番。 - -o filename
SQLの実行結果のみ指定ファイルに出力する。
実行したSQL自体は標準出力される。 - -p port
ポートの指定 - -d dbname 接続先DBの指定。psqlコマンドの最初の引数も接続先DBの指定のため、どちらかにすること。
- -U username
ユーザの指定。指定しなければデフォルトユーザで接続。 - -w
パスワードを不要とする。セッション中はずっと不要となる。バッチ処理には必要ですね。
サーバがパスワード認証を必要とし、かつ、.pgpassファイルなどの他の方法が利用できない場合、接続試行は失敗します。 (引用：公式)
- -t
問い合わせの結果列数や見出しを出力させない。バッチ処理で使えそう。 - -F separator
区切り文字の設定。タブ区切りにしようとして、2つもハマった。
- -Aと組み合わせないとデフォルトの「|」(バーティカルバー)から区切り文字を変更できない
- \tを認識してくれない(「-F$'\t'」で回避)
(参照：備忘録：psqlでタブを区切り文字として利用)
バッチでの使用例
psql -U userName -w -h hostName -d dbName -p portNumber -AF$'\t' -a -c command -o filename
または
psql -U userName -w -h hostName -d dbName -p portNumber -AF$'\t' -a -f commandFile -o filename
と、シェルに書いておいて、シェルをリダイレクト実行でしょうか。
test.sh > execute.log
-a つけておけば、実際に実行されたSQL文がexecute.logに出力されます。
\コマンド
- \?
ヘルプ - \h SQLコマンド名
SQLコマンドのヘルプを表示 - \d
テーブル、ビュー、シーケンスの一覧を表示 - \d table_name
SQLPLUSで言う所の、「describe」 - \dt
テーブルの一覧を表示 - \i sql_file_name
ファイルからSQL実行 - \e [file]
引数なしでバッファに残っている(直前の)SQLを編集できます。
ファイルを指定すれば、ファイルを指定できます。 - \x
拡張表示となる。表形式ではなく、１レコードずつキーと値の形式で出力される。
設定・管理系のSQL・クライアントアプリケーションコマンド
ちょっと混乱していましたが、Postgresqlには、psqlのようにPostgreSQLクライアントアプリケーションのコマンドがあります。これはSQLコマンドとは別です。Oracle SQL*PlusのDESCRIBEコマンドみたいなもんですかね。クライアントアプリは改行までで１文ですが、SQLは「；」までで1文です。
SQL
- SELECT * FROM pg_shadow; ユーザー一覧
- CREATE DATABASE データベース名;
- DROP TABLE テーブル名;
- DROP USER ユーザ名 ;
データベースロールを削除する
実際には「DROP ROLE」が呼び出される
クライアントアプリコマンド
テーブルの操作はできなそう。
- dropdb データベース名
- createdb データベース名
- dropuser PostgreSQLユーザ名
PostgreSQLユーザアカウントを削除する
重たいクエリを投げてしまった時の対処
単純にkill -9 プロセス番号でプロセスを終了させればいいてもんじゃない。
ロック開放待ちがたまってDBが落ちる可能性があります。
- 実行したSQLのプロセスIDと実行時間を確認(アクティブなプロセスを探す)
- 該当プロセスの停止
SELECT RANDOM() FROM GENERATE_SERIES(1, 1000000000000000000000000);
SELECT
pid, query_start, substr(query, 0, 40)
FROM
pg_stat_activity
WHERE
state='active'
ORDER BY
query_start;
pid | query_start | substr
4655 | 2016-11-23 19:40:58.875762+09 | SELECT RANDOM() FROM GENERATE_SERIES(1,
4385 | 2016-11-23 19:41:00.849544+09 | SELECT pid, query_start, substr(query,
SELECT pg_cancel_backend(4655);
pg_cancel_backend
t
参照：現在実行中のSQLクエリを殺す流れ
参照：PostgreSQL: 集合を返すGENERATE_SERIES関数で大量データを生成して集計する
頻出関数
| 関数名 | 動き | 使用例 |
|---|---|---|
| NOW() |
コメント
- 「--」1行コメント
- 「/* */」複数行コメント