はじめに
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を作成
- 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
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
- Postgres Cheatsheet
- PostgreSQL command line cheatsheet
- PostgreSQLシェルスクリプトでSQLに引数を渡して実行
- sql - Reverse initial order of SELECT statement - Stack Overflow