Open19
postgresqlコマンド
haseyuyデータベース
PostgreSQLでは複数のデータベースを管理することができる。
インストールした直後はテンプレート用のデータベースであるtemplate0 と template1 、そして postgres という名前のデータベースが作成されている。
haseyuy縦に整列する
selectの結果が長い時に、expanded display modeをonにすることで結果が縦に整列して表示される
db=# \x auto
expanded display modeをOffするとき
db=# \x off
データベース一覧表示
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
データベースの作成
postgres=# CREATE DATABASE db01;
CREATE DATABASE
データベースの作成
postgres=# \c db01
You are now connected to database "db01" as user "haseyuy".
db01=#
新しいテーブルの作成
- テーブル名と、テーブル全ての列の名前と型をしてする。
-
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
テーブルに含まれるカラム一覧の取得
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 | | |
データの追加
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)
デフォルト値の追加
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)
PostgreSQLにおけるデータベース、スキーマ、テーブルの関係
haseyuyスキャン方法
Seq Scan(シーケンシャルスキャン)
- テーブルの最初から最後までシーケンシャルにアクセスする。
Index Scan(インデックススキャン)
- インデックスとテーブルを交互にランダムにアクセスする。WHERE句による絞り込みによる取り出す件数が少ない場合や目的のデータにピンポイントにアクセスしたい場合に有効な方法。
haseyuyEXPLAINと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システムテーブルに結果が保存される。
haseyuySELECT結果の値だけを取得する
-
-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@mac ~ % psql -Atq -F ',' -h localhost -p 5432 -U haseyuy -d db01 -c "select * from weather;" | cut -d , -f -1
東京
テンプレートデータベース
- 新しく作成(CREATE DATABASE)するデータベースの元になるテンプレート
- デフォルトでは
template1という名前の標準のシステムデータベースをコピーする - つまり新しいデータベースに常に入れておきたい共通関数などをここに入れておけば、create後に改めて作らなくてよいということになる
-
template1の他にtemplate0も存在する -
template0は、システム標準のオブジェクトのみが含まれるため更新や削除はできない
haseyuypublicスキーマ
- 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ストアドプロシージャ
- 実行したいSQLをデータベースに格納しておき、ストアドプロシージャを呼び出すだけで一連の処理を実行することができる
- 一回の要求で複数のSQL分が実行でき、ネットワーク負荷がかからず処理時間が短縮できる
- 汎用的な処理が共通化できる
-
CREATE PROCEDUREで新たなプロシージャを定義する