LoginSignup
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@gkzz(Gakuji Tamaki)in株式会社エーピーコミュニケーションズ

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

Last updated at Posted at 2019-11-03

はじめに

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=>

参考

P.S. Twitterもやってるのでフォローしていただけると泣いて喜びます:)

@gkzvoice

#gkz

1
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?