逆引きPostgreSQLコマンドチートシート[随時更新][sqlに引数を渡す方法]

# はじめに
**PostgreSQL**

雰囲気で触っていませんか？
僕もそんなひとりです。
コマンドを理解しなくちゃいけないのですが、飲み込みが遅く絶賛苦戦中です汗。

そこで、重すぎる腰を起こして(オレオレ)チートシートを作ることにしました！
間違いがありましたら、あるいはより適切な方法があればコメントしていただけるとうれしいです。


## TL;DR

```
gkz@localhost $ git clone https://github.com/gkzz/pg_cheatsheet.git
gkz@localhost $ cd pg_cheatsheet
gkz@localhost ~/pg_cheatsheet (master) $
gkz@localhost ~/pg_cheatsheet (master) $ docker-compose up -d --build
gkz@localhost ~/pg_cheatsheet (master) $ docker-compose exec pg  bash
root@pg12:/# su - postgres
postgres@pg12:~$ psql
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=# 

```


## 環境/バージョン情報
- Ubuntu 19.04 (Disco Dingo)
- Docker 19.03.4
  - postgres (PostgreSQL) 12.0 (Debian 12.0-2.pgdg100+1)
- docker-compose 1.24.1


## 逆引きPostgreSQLコマンドチートシート目次
- [DBユーザーとDBを作成](https://qiita.com/gkzz/items/2be5be2f136532cb50d3#DBユーザーとDBを作成)
- [データベースに接続](https://qiita.com/gkzz/items/2be5be2f136532cb50d3#データベースに接続)
- [テーブルを作成](https://qiita.com/gkzz/items/2be5be2f136532cb50d3#テーブルを作成)
- [データベースに接続してから実行するバックスラッシュ系コマンド](https://qiita.com/gkzz/items/2be5be2f136532cb50d3#データベースに接続してから実行するバックスラッシュ系コマンド)

### DBユーザーとDBを作成
- postgres@pg12:~\$ createuser \$USERNAME
- postgres@pg12:~\$ createdb -O \$USERNAME -E \$ENCODING --locale=\$LOCALE -T template0 $DBNAME

```
root@pg12:/# su - postgres
postgres@pg12:~$ createuser tempuser1
postgres@pg12:~$ createdb -O tempuser1 -E UTF8 --locale=C -T template0 tempdb1
postgres@pg12:~$ psql -l
                                 List of databases
   Name    |   Owner   | Encoding |  Collate   |   Ctype    |   Access privileges  
 
-----------+-----------+----------+------------+------------+----------------------
-
 postgres  | postgres  | UTF8     | en_US.utf8 | en_US.utf8 | 
 tempdb1   | tempuser1 | UTF8     | C          | C          | 
 template0 | postgres  | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          
+
           |           |          |            |            | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          
+
           |           |          |            |            | postgres=CTc/postgres
(4 rows)

postgres@pg12:~$ 
```

rootから直接行う場合、**-U postgres** を追加すればいいらしい。
参考: [わかりみSQL - Kauplan Press - BOOTH](https://booth.pm/ja/items/1576397)

```
root@pg12:~$ createuser -U postgres tempuser1
root@pg12:~$ createdb -U postgres -O tempuser1 -E UTF8 --locale=C -T template0 tempdb1
```

### データベースに接続
- postgres=# \connect $DBNAME

```
root@pg12:/# su - postgres
postgres@pg12:~$ psql
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.

postgres=# \connect tempdb1
You are now connected to database "tempdb1" as user "postgres".
tempdb1=# 
```

ここでもDBユーザーやデータベースの作成と同様に、**-U postgres**を追加すれば、rootから直接接続することも出来た。

```
root@pg12:/# psql -U tempuser1 tempdb1
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.

tempdb1=>
```

### テーブルを作成
- tempdb1=> \i /path/to/$SQLFILE

```
root@pg12:/# psql -U tempuser1 tempdb1
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.

tempdb1=> \i /usr/local/src/postgres12/create-stafftable1.sql
CREATE TABLE
tempdb1=> \dt
            List of relations
 Schema |    Name     | Type  |   Owner   
--------+-------------+-------+-----------
 public | stafftable1 | table | tempuser1
(1 row)

tempdb1=> 
```
#データベースに接続してから実行するバックスラッシュ系コマンド

- list tables, views, and sequencesを表示
  - \d
- $TABLENAMEのみ表示したい場合
  - \d $TABLENAME

```
tempdb1-# \d stafftable1
               Table "public.stafftable1"
 Column |     Type     | Collation | Nullable | Default 
--------+--------------+-----------+----------+---------
 id     | character(4) |           | not null | 
 name   | text         |           | not null | 
 age    | integer      |           |          | 
Indexes:
    "stafftable1_pkey" PRIMARY KEY, btree (id)
    "stafftable1_name_key" UNIQUE CONSTRAINT, btree (name)
```

- \dより詳細に
  - \d+ 
 
```
tempdb1-# \d+ stafftable1
                                   Table "public.stafftable1"
 Column |     Type     | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+--------------+-----------+----------+---------+----------+--------------+-------------
 id     | character(4) |           | not null |         | extended |              | 
 name   | text         |           | not null |         | extended |              | 
 age    | integer      |           |          |         | plain    |              | 
Indexes:
    "stafftable1_pkey" PRIMARY KEY, btree (id)
    "stafftable1_name_key" UNIQUE CONSTRAINT, btree (name)
Access method: heap

tempdb1-# 
```
- Quit(離脱)
  - \q

コマンドのオプションでわからなくなったら、
**--help~~**とつけるとオプション名とオプションの内容がかえってくるのでおすすめです。

```
root@pg12:/# psql --help | head
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "root")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
root@pg12:/# 
```

バックスラッシュ系でなんてググればいいかわからない場合は
**\?** と叩くといいかもです。

```
root@pg12:/# su - postgres
postgres@pg12:~$ psql
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.

postgres=# \? 
中略

  \dRp[+] [PATTERN]      list replication publications
  \dRs[+] [PATTERN]      list replication subscriptions
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[S+] [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dx[+]  [PATTERN]      list extensions
  \dy     [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+]  FUNCNAME       show a function's definition
  \sv[+]  VIEWNAME       show a view's definition
  \z      [PATTERN]      same as \dp

以下略　
```

## 2020/01/01更新

ユーザー設定やデータベース作成、最低限のselectコマンドをシェルスクリプトとsqlファイルに落とし込みました。

```
gkz@localhost ~pg_cheatsheet (master) $ tree pg/src/
pg/src/
├── init.sh                      # ユーザー設定やデータベース作成
├── table1                       # table1作成
│   └── create.sql
└── table2
    ├── create.sql               # table2作成
    ├── insert.sql               # table2にデータ入力
    ├── select_all.sql           # select *
    └── select_name_geneder.sql  # select gender, name
```

- postgesqlコンテナ立ち上げ

```
gkz@localhost ~/pg_cheatsheet (master) $ docker-compose up -d --build
gkz@localhost ~/pg_cheatsheet (master) $ docker-compose exec pg  bash
```

- ユーザー設定やデータベース作成からデータ入力、selectコマンドで値確認

```
root@pg12:/# . docker-entrypoint-initdb.d/init.sh 
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

tempdb1=> \i docker-entrypoint-initdb.d/table2/create.sql 
CREATE TABLE
tempdb1=> \i docker-entrypoint-initdb.d/table2/insert.sql 
INSERT 0 7
tempdb1=> \i docker-entrypoint-initdb.d/table2/select_all.sql 
 id |  name   | height | gender 
----+---------+--------+--------
  1 | Alice   |    110 | f
  2 | Bob     |    120 | m
  3 | Charlie |    130 | m
  4 | David   |    140 | m
  5 | Eve     |    150 | f
  6 | Frank   |    160 | m
  7 | Grace   |    170 | m
(7 rows)

tempdb1=> \i docker-entrypoint-initdb.d/table2/select_name_geneder.sql 
 gender |  name   
--------+---------
 f      | Alice
 m      | Bob
 m      | Charlie
 m      | David
 f      | Eve
 m      | Frank
 m      | Grace
(7 rows)

tempdb1=> 
```

### sqlに引数を渡す方法

- 引数を渡すsqlファイル

```select.sql
select :sth
from table2;
```

```./docker-entrypoint-initdb.d/table2/select.sh
#!/bin/bash

key=$1

username=tempuser1
dbname=tempdb1

psql -U $username $dbname \
< "./docker-entrypoint-initdb.d/table2/select.sql" \
-v sth=$key
```

- 任意の引数を渡してsqlを実行

```
root@pg12:/# . docker-entrypoint-initdb.d/table2/select.sh name
  name   
---------
 Alice
 Bob
 Charlie
 David
 Eve
 Frank
 Grace
(7 rows)
```

- 全ての行(* アスタリスク)を渡してsqlを実行する場合は、アスタリスクの前に`エスケープ`を渡す必要がある

```
root@pg12:/# . docker-entrypoint-initdb.d/table2/select.sh \*
 id |  name   | height | gender 
----+---------+--------+--------
  1 | Alice   |    110 | f
  2 | Bob     |    120 | m
  3 | Charlie |    130 | m
  4 | David   |    140 | m
  5 | Eve     |    150 | f
  6 | Frank   |    160 | m
  7 | Grace   |    170 | m
(7 rows)

root@pg12:/# 
```

### 昇順/降順でソートする方法

- 昇順

```
tempdb1=> select * from table2 order by height asc;
 id |  name   | height | gender 
----+---------+--------+--------
  1 | Alice   |    110 | f
  2 | Bob     |    120 | m
  3 | Charlie |    130 | m
  4 | David   |    140 | m
  5 | Eve     |    150 | f
  6 | Frank   |    160 | m
  7 | Grace   |    170 | m
(7 rows)

tempdb1=> 
```

- 降順

```
tempdb1=> select * from table2 order by height desc;
 id |  name   | height | gender 
----+---------+--------+--------
  7 | Grace   |    170 | m
  6 | Frank   |    160 | m
  5 | Eve     |    150 | f
  4 | David   |    140 | m
  3 | Charlie |    130 | m
  2 | Bob     |    120 | m
  1 | Alice   |    110 | f
(7 rows)
```

### 平均値を算出する方法

```
tempdb1=> select gender, avg(height) from table2 group by gender order by avg asc;
 gender |         avg          
--------+----------------------
 f      | 130.0000000000000000
 m      | 144.0000000000000000
(2 rows)

tempdb1=> select gender, avg(height) from table2 group by gender order by avg desc;
 gender |         avg          
--------+----------------------
 m      | 144.0000000000000000
 f      | 130.0000000000000000
(2 rows)

tempdb1=> 
```


- 少数第N位を指定する
  - `to_char(avg(height), '000.00')`
  - 整数の桁が正しくないとダメらしい？
- to_charから変更する`
  - `as $NAME`
 
```                                                            ^
tempdb1=> select gender, to_char(avg(height), '000.00') from table2 group by gender order by to_char asc;
 gender | to_char 
--------+---------
 f      |  130.00
 m      |  144.00
(2 rows)

tempdb1=> select gender, to_char(avg(height), '000.00') from table2 group by gender order by to_char desc;
 gender | to_char 
--------+---------
 m      |  144.00
 f      |  130.00
(2 rows)


tempdb1=> select gender, to_char(avg(height), '0.00') from table2 group by gender order by to_char desc;
 gender | to_char 
--------+---------
 m      |  #.##
 f      |  #.##
(2 rows)

tempdb1=> select gender, to_char(avg(height), '000') as avg_height from table2 group by gender order by avg_height desc; 
gender | avg_height 
--------+------------
 m      |  144
 f      |  130
(2 rows)

tempdb1=> 
```

- ひとつのsqlファイルに複数sqlコマンドを実行することもできる
  - いつ使うんだよ、、、。

```docker-entrypoint-initdb.d/table2/group.sql 
select gender, name
from table2;

select gender, 
       to_char(avg(height), '000') as avg_height 
from table2 
group by gender 
order by avg_height desc; 
```

```
tempdb1=> \i docker-entrypoint-initdb.d/table2/group.sql 
 gender |  name   
--------+---------
 f      | Alice
 m      | Bob
 m      | Charlie
 m      | David
 f      | Eve
 m      | Frank
 m      | Grace
(7 rows)

 gender | avg_height 
--------+------------
 m      |  144
 f      |  130
(2 rows)

tempdb1=> 
``` 


### 任意の項目の個数を求める


```
tempdb1=> select name, gender from table2;
  name   | gender 
---------+--------
 Alice   | f
 Bob     | m
 Charlie | m
 David   | m
 Eve     | f
 Frank   | m
 Grace   | m
(7 rows)
tempdb1=> select gender, count(*) from table2 group by gender;
 gender | count 
--------+-------
 m      |     5
 f      |     2
(2 rows)

tempdb1=> select gender, count(*) from table2 group by gender having gender = 'f';
 gender | count 
--------+-------
 f      |     2
(1 row)

tempdb1=> select gender, count(*) from table2 group by gender having gender = 'm';
 gender | count 
--------+-------
 m      |     5
(1 row)

tempdb1=> 
```


## 参考
- [わかりみSQL - Kauplan Press - BOOTH](https://booth.pm/ja/items/1576397)
- [Postgres Cheatsheet](https://gist.github.com/apolloclark/ea5466d5929e63043dcf)
- [PostgreSQL command line cheatsheet](https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546)
- [PostgreSQLシェルスクリプトでSQLに引数を渡して実行](https://qiita.com/A-Kira/items/dde43e0e0aa7cec51938)
- [sql - Reverse initial order of SELECT statement - Stack Overflow](https://stackoverflow.com/questions/37979900/reverse-initial-order-of-select-statement)


## P.S. Twitterもやってるのでフォローしていただけると泣いて喜びます:)
[@gkzvoice](https://twitter.com/gkzvoice)

[#gkz](https://twitter.com/hashtag/gkz)