postgresqlコマンド

haseyuy 2021/11/28

データベース

PostgreSQLでは複数のデータベースを管理することができる。
インストールした直後はテンプレート用のデータベースであるtemplate0 と template1 、そして postgres という名前のデータベースが作成されている。

haseyuy 2021/11/28

縦に整列する

selectの結果が長い時に、expanded display modeをonにすることで結果が縦に整列して表示される

db=# \x auto

haseyuy 2021/11/28

expanded display modeをOffするとき

db=# \x off

haseyuy 2021/11/28

データベース一覧表示

postgres=# \l
List of databases
-[ RECORD 1 ]-----+--------------------
Name              | postgres
Owner             | haseyuy
Encoding          | UTF8
Collate           | C
Ctype             | C
Access privileges |
-[ RECORD 2 ]-----+--------------------
Name              | template0
Owner             | haseyuy
Encoding          | UTF8
Collate           | C
Ctype             | C
Access privileges | =c/haseyuy         +
                  | haseyuy=CTc/haseyuy
-[ RECORD 3 ]-----+--------------------
Name              | template1
Owner             | haseyuy
Encoding          | UTF8
Collate           | C
Ctype             | C
Access privileges | =c/haseyuy         +
                  | haseyuy=CTc/haseyuy

haseyuy 2021/11/28

データベースの作成

postgres=# CREATE DATABASE db01;
CREATE DATABASE

データベースの作成

postgres=# \c db01
You are now connected to database "db01" as user "haseyuy".
db01=#

haseyuy 2021/11/28

新しいテーブルの作成

テーブル名と、テーブル全ての列の名前と型をしてする。
varvhar(80)は、80文字までに任意の文字列を格納できるデータ型の指定。

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);

テーブルの確認

db01=# \dt
List of relations
-[ RECORD 1 ]---
Schema | public
Name   | weather
Type   | table
Owner  | haseyuy

haseyuy 2021/11/28に更新

テーブルに含まれるカラム一覧の取得

db01=\# \d weather
                      Table "public.weather"
 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 city    | character varying(80) |           |          |
 temp_lo | integer               |           |          |
 temp_hi | integer               |           |          |
 prcp    | real                  |           |          |
 date    | date                  |           |          |

haseyuy 2021/11/28に更新

データの追加

db01=\# INSERT INTO weather VALUES('東京',15,28,15,NOW());
INSERT 0 1
db01=\# select * from weather;
 city | temp_lo | temp_hi | prcp |    date
------+---------+---------+------+------------
 東京 |      15 |      28 |   15 | 2021-11-28
(1 row)

haseyuy 2021/11/28に更新

デフォルト値の追加

db01=\# INSERT INTO weather default values;;
INSERT 0 1
db01=\# select * from weather;
 city | temp_lo | temp_hi | prcp |    date
------+---------+---------+------+------------
 東京 |      15 |      28 |   15 | 2021-11-28
      |         |         |      |
(2 rows)

haseyuy 2021/11/28

PostgreSQLにおけるデータベース、スキーマ、テーブルの関係

haseyuy 2021/11/28

スキーマとは、データベースに作成されるテーブルや関数といったオブジェクトをグループ化するもの。
スキーマの中にテーブルを作成する。
スキーマが異なれば同じテーブル名のテーブルでも作成することができる。

haseyuy 2022/01/12

スキーマは、本質的には名前空間

haseyuy 2021/11/28

スキャン方法

Seq Scan(シーケンシャルスキャン)

テーブルの最初から最後までシーケンシャルにアクセスする。

Index Scan(インデックススキャン)

インデックスとテーブルを交互にランダムにアクセスする。WHERE句による絞り込みによる取り出す件数が少ない場合や目的のデータにピンポイントにアクセスしたい場合に有効な方法。

haseyuy 2021/11/28に更新

EXPLAINとEXPLAIN ANALYZE

EXPLAIN

推定された実行計画を表示する。
Seq Scan方式でスキャン。
cost=A..Bは、Aが最初の行を返すまでのコストと、Bが全ての行を返し終わるまでのコスト。
rowsは、推測された問い合わせ結果の行数。
widthは、推測された入力サイズ。

db01=# EXPLAIN SELECT count(*) from weather;
                           QUERY PLAN
----------------------------------------------------------------
 Aggregate  (cost=14.50..14.51 rows=1 width=8)
   ->  Seq Scan on weather  (cost=0.00..13.60 rows=360 width=0)
(2 rows)

EXPLAIN ANALYZE

推定でなく、一度実行してみた結果を表示する。

db01=\# EXPLAIN ANALYZE SELECT count(*) from weather;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=14.50..14.51 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
   ->  Seq Scan on weather  (cost=0.00..13.60 rows=360 width=0) (actual time=0.019..0.021 rows=2 loops=1)
 Planning Time: 0.121 ms
 Execution Time: 0.091 ms
(4 rows)

ANALYZE

EXPLAIN ANALYZEで推定と実測に乖離があったので、ANALYZEコマンドを使う。
データベース内のテーブル内容に関する統計情報を更新するコマンド。
pg_staticsシステムテーブルに結果が保存される。

haseyuy 2021/11/28

SELECT結果の値だけを取得する

-Atqcオプションを使用すると、値だけを取得することができる。

haseyuy@mac ~ %  psql -Atq -h localhost -p 5432 -U haseyuy -d db01 -c  "select * from weather;"
東京|15|28|15|2021-11-28
||||

-Fオプションを使用すると、区切り文字を変更できる。

haseyuy@mac ~ %  psql -Atq -F ',' -h localhost -p 5432 -U haseyuy -d db01 -c  "select * from weather;"
東京,15,28,15,2021-11-28
,,,,

haseyuy 2021/11/28

こんな風にパイプで繋げることができる。

haseyuy@mac ~ % psql -Atq -F ',' -h localhost -p 5432 -U haseyuy -d db01 -c  "select * from weather;" | cut -d , -f -1
東京

haseyuy 2022/01/06

テンプレートデータベース

新しく作成(CREATE DATABASE)するデータベースの元になるテンプレート
デフォルトではtemplate1という名前の標準のシステムデータベースをコピーする
つまり新しいデータベースに常に入れておきたい共通関数などをここに入れておけば、create後に改めて作らなくてよいということになる
template1の他にtemplate0も存在する
template0は、システム標準のオブジェクトのみが含まれるため更新や削除はできない

haseyuy 2022/01/11に更新

publicスキーマ

Public スキーマは誰でもアクセスできるスキーマ、DB 構築時にデフォルトで存在する
Public スキーマの所有者は template0 の所有者

testdb=> \l
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema
        |          | =UC/rdsadmin         | 
(1 row)

アクセス権限	意味
rdsadmin=UC/rdsadmin+	rdsadmin によって rdsadmin の U=Usage,C=Create を許可
=UC/rdsadmin	rdsadmin によって public ロールの U=Usage,C=Create を許可

haseyuy 2022/01/23

ストアドプロシージャ

実行したいSQLをデータベースに格納しておき、ストアドプロシージャを呼び出すだけで一連の処理を実行することができる
一回の要求で複数のSQL分が実行でき、ネットワーク負荷がかからず処理時間が短縮できる
汎用的な処理が共通化できる
CREATE PROCEDUREで新たなプロシージャを定義する