PostgreSQL on MacOS with pgAdmin4 テストメモ

# :elephant:PostgreSQL on :apple: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ではタブを切り替える必要があります。なんか設定ないかな。。。

### :zap:QueryTool  
なんか名前と場所が変わって見つけづらかったSQLビルダーです。
:zap:マークをたどっていけば、SQLを実行できます。F5でクエリ実行は健在です。

#### ctrl+space  
入力補完が出る！！！入力補完が出る！！！


#### タブは2文字にしたい
  
- メニューバーから「File」をクリック
    - ドロップダウンメニューから「Preference」
        - SQL EditorのOptionでTab Sizeを変更
            
#### クエリツールのExplain  
実行するクエリの分析をしてくれます. 
コストも表示されていたので、なかなか使えそうです。
  
- クエリツールの実行ボタン:zap:横のドロップダウンメニュー
    - Explainを実行
        - 右下のパネルのExplainタブを選択

#### クエリツールの右下のパネルの横スクロールバーが表示されない
自分の環境では、横スクロールはできるがバーは表示されませんでした。


#### メニューバーのメニューはクリックで開閉
見た目がWebサイトっぽいけど、オンマウスでドロップダウンメニューを開くってことはできないみたいです。ドロップダウンメニューを開いたけど、他のパネルに移動した時にメニューが開いたままになる。

#### SELECT文の結果をコピーできない
環境問題だと思いますが、Rowを選択して、コピーのショートカットやコピーアイコンを押しても結果がコピーできませんでした。CSVに出力するしかない。


## psqlコマンド
- psql db_name  
データベースに接続  
postgres-# のように、プロンプトがdb_nameになります。

### オプション
詳細は[PostgreSQL 9.5.4文書 PostgreSQLクライアントアプリケーション psql](https://www.postgresql.jp/document/9.5/html/app-psql.html)参照

- -l  
データベースの一覧を表示させる
-  -a  
読み込んだ行を全て出力  
つまり、実行したSQLが標準出力される。
- -f filename  
ファイルに書き込んだSQLを実行。\iと同様。  
「psql < filename」との違いは[公式](https://www.postgresql.jp/document/9.5/html/app-psql.html)参照。
- -h hostname  
SQLサーバのホスト名を指定
- -L filename  
全ての出力を標準出力と指定ファイルに出力する。**L**ogfileです。  
実行したSQLもファイルに含まれてしまうので、データを加工したい時には向かない。  
-oの出番。
- -o filename  
SQLの実行結果のみ指定ファイルに出力する。  
実行したSQL自体は標準出力される。
- -p port  
ポートの指定
- -d dbname
接続先DBの指定。psqlコマンドの最初の引数も接続先DBの指定のため、どちらかにすること。
- -U username  
ユーザの指定。指定しなければデフォルトユーザで接続。
-  -w    
パスワードを不要とする。セッション中はずっと不要となる。バッチ処理には必要ですね。

> サーバがパスワード認証を必要とし、かつ、**.pgpassファイル**などの他の方法が利用できない場合、接続試行は失敗します。 (引用：[公式](https://www.postgresql.jp/document/9.5/html/app-psql.html))

- -t  
問い合わせの結果列数や見出しを出力させない。バッチ処理で使えそう。
- -F separator  
区切り文字の設定。タブ区切りにしようとして、2つもハマった。

1. -Aと組み合わせないとデフォルトの「|」(バーティカルバー)から区切り文字を変更できない
1. \tを認識してくれない(「-F$'\t'」で回避)  
(参照：[備忘録：psqlでタブを区切り文字として利用](http://qiita.com/kiseragi/items/786093a6d43860ab5e80))

### バッチでの使用例

```SQL:sample
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が落ちる可能性があります。

1. 実行したSQLのプロセスIDと実行時間を確認(アクティブなプロセスを探す)
2. 該当プロセスの停止  
 
```SQL:重たいクエリ
SELECT RANDOM() FROM GENERATE_SERIES(1, 1000000000000000000000000);
```

```SQL:1.confirm
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, 
```

```SQL:2.kill
SELECT pg_cancel_backend(4655);

 pg_cancel_backend 
 t
```

参照：[現在実行中のSQLクエリを殺す流れ
](http://qiita.com/awakia/items/b72988b94084eeb6d280)参照：[PostgreSQL: 集合を返すGENERATE_SERIES関数で大量データを生成して集計する](http://www.techscore.com/blog/2013/03/12/集合を返すgenerate_series関数で大量データを生成して集計/)


## 頻出関数
|関数名|動き        |使用例           |
|:--|:----------|:-------------|
|NOW()||


## コメント
- 「--」1行コメント
- 「/* */」複数行コメント

## 参考
[公式ドキュメント pgAdmin 4 1.1 documentation](https://www.pgadmin.org/docs4/1.x/index.html)