# Ping-TにてOSSDBの勉強
###### tags: `postgresql`
## 環境変数
都度入力せずに下記を環境変数に設定することで、入力を省略できる。
PGUSER : 接続ユーザー
PGPASSWORD : パスワード
PGHOST : ホスト
PGDATABASE : データベース
PGPORT : ポート
## バックアップ
### `pg_dump`コマンド
データベース単位でバックアップする。(テーブル単位でもできるらしい。)
```bash
$ pg_dump -h [ホスト名] -p [ポート] -U [ユーザー名] -d [データベース名] -F[p or c or t] -f [バックアップファイル名]
```
※「-F」のオプションにはプレーンテキストの場合「p」、バイナリの場合「c」、tarの場合「t」。
※テキスト形式でバックアップした場合のみ`psql`コマンドでリストアできるが、それ以外は`pg_restore`コマンドを使用する。
### `pg_dumpall`コマンド
データベースに加えて、ロールやテーブルスペース等もバックアップする。(全データのバックアップ)
```bash
$ pg_dumpall -h [ホスト名] -p [ポート] -U [ユーザー名] -f [バックアップファイル名]
```
※全てのデータをバックアップするためスーパーユーザーで実施するのが推奨。もしそうでない場合、**何回かパスワードを求められるので都度入力する必要がある。**
## リストア
### `psql`コマンド
```bash
$ psql -U [ユーザー名] -h [ホスト名] -p [ポート] -f [バックアップファイル(テキスト形式)] [データベース名]
```
### `pg_restore`コマンド
テキスト形式以外のリストアを行うコマンド。
| オプション | 説明 |
| ---------- | ------------------------------------------------------ |
| -c | リストア前に既存のデータベースオブジェクトを削除する。 |
| -j [ジョブ数]| リストアを実行するジョブ数。tar形式のバックアップファイルだと使用できない。|
| -1 or --single-transaction|リストアを1つのトランザクションとして実行する。-jオプションと併用できない。|
## データ型書式設定関数
| 関数 | 説明 |
| ---- | ---- |
| to_char(値, 'パータン')|指定された値を文字列に変換する。|
| to_date(文字列, 'パータン')|指定された文字列を日付型に変換する。|
| to_timestamp(文字列, 'パータン') |指定された文字列をタイムスタンプ型に変換する。|
| to_number(文字列, 'パータン') |指定された文字列を数値型に変換する。|
【日付・時刻型に変換する場合の書式パターン】
YYYY：4桁以上の年を表す
MM：01~12の月番号を表す
DD：01~31の日にち番号を表す
HH：01~12の時を表す
HH12：01~12の時を表す
HH24：00~23の時を表す
MI：00~59の分を表す
SS：00~59の秒を表す
MS：000~999のミリ秒を表す
US：000000~999999のマイクロ秒を表す
MONTH：大文字での月名を表す
MON：短縮形の大文字での月名を表す
DAY：大文字での曜日名を表す
DY：短縮形の大文字での曜日名を表す
【数値型に変換する場合の書式パターン】
9：指定された桁数での値を返す
999と指定した場合は3桁の値で返される
0：前に0が付いた値を表す
0999としていた場合は、使用しない桁数も4桁目まで0で埋めて返される
.(ピリオド)：小数点を表す
,(カンマ)：千単位で区切る符号を表す
## 集合演算
2つのSELECTの結果を結合する。主にUNION、EXCEPT、INTERSECTが存在する。
### UNION
ALL省略の場合、左右の結果から重複を省いて表示する。
ALLの場合、重複するデータも表示する。
```sql
SELECT ~ UNION [ALL] SELECT ~
-- 例：
SELECT member FROM project_a UNION ALL SELECT member FROM project_b;
member
---------
A社社員
A社社員
B社社員
C社社員
C社社員
C社社員
C社社員
C社社員
D社社員
(9 rows)
重複も表示されている。
SELECT member FROM project_a UNION SELECT member FROM project_b;
member
---------
B社社員
A社社員
D社社員
C社社員
(4 rows)
重複が省略されている。
```
### EXCEPT
ALL省略の場合、左右の結果から重複を省いて表示する。
ALLの場合、重複するデータも表示する。
```sql
SELECT ~ EXCEPT [ALL] SELECT ~
```
左の結果 - 右の結果
```sql
sample=# SELECT member FROM project_a EXCEPT SELECT member FROM project_b;
member
---------
B社社員
A社社員
(2 rows)
sample=# SELECT member FROM project_a EXCEPT ALL SELECT member FROM project_b;
member
---------
B社社員
A社社員
A社社員
(3 rows)
sample=#
```
### INTERSECT
ALL省略の場合、左右の結果から重複を省いて表示する。
ALLの場合、重複するデータも表示する。
```sql
SELECT ~ INTERSECT [ALL] SELECT ~
```
両方のSELECTの共通するデータを返す。
```sql
sample=# SELECT member FROM project_a INTERSECT SELECT member FROM project_b;
member
---------
C社社員
(1 row)
sample=# SELECT member FROM project_a INTERSECT ALL SELECT member FROM project_b;
member
---------
C社社員
C社社員
(2 rows)
sample=#
```
## ドメイン(DOMAIN)
同じ制約条件(NULL/NOT NULL/CHECK)で独自のデータ型を定義することができる。
### 作成したドメインの確認
`\dD`
### ドメインの作成
```sql
--- ドメインの作成
sample=# CREATE DOMAIN origin_type AS int DEFAULT 10
CONSTRAINT origin_type_check CHECK (VALUE >= 10);
CREATE DOMAIN
sample=#
--- ドメインの確認
sample=#
sample=# \dD
List of domains
Schema | Name | Type | Collation | Nullable | Default | Check
--------+-------------+---------+-----------+----------+---------+---------------------
public | origin_type | integer | | | 10 | CHECK (VALUE >= 10)
(1 row)
--- ドメインの適用
sample=#
sample=# create table test_domain(id origin_type, name varchar(45));
CREATE TABLE
sample=# \d test_domain
Table "public.test_domain"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | origin_type | | |
name | character varying(45) | | |
--- ドメインの動作確認
sample=# insert into test_domain(name) values('test');
INSERT 0 1
sample=# select * from test_domain;
id | name
----+------
10 | test
(1 row)
sample=#
sample=#
sample=# insert into test_domain values(5, 'test');
ERROR: value for domain origin_type violates check constraint "origin_type_check"
sample=#
sample=#
sample=#
```
## CREATE TABLE
```sql
--- 主キー制約を設定する場合
CREATE TABLE テーブル名 (カラム名 データ型 PRIMARY KEY);
CREATE TABLE テーブル名 (カラム名 データ型[, カラム名 データ型...], [CONSTRAINT 制約名] PRIMARY KEY (カラム名[, カラム名...]));
--- ユニーク制約を設定する場合
CREATE TABLE テーブル名 (カラム名 データ型 UNIQUE);
CREATE TABLE テーブル名 (カラム名 データ型[, カラム名 データ型...], [CONSTRAINT 制約名] UNIQUE (カラム名[, カラム名...]));
--- NOT NULL制約を設定する場合
CREATE TABLE テーブル名 (カラム名 データ型 NOT NULL);
--- 外部キー制約を設定する場合
CREATE TABLE テーブル名 (カラム名 データ型 REFERENCES 参照先テーブル名 (カラム名));
CREATE TABLE テーブル名 (カラム名 データ型[, カラム名 データ型...], [CONSTRAINT 制約名] FOREIGN KEY (カラム名) REFERENCES 参照先テーブル名 (カラム名[, カラム名...]));
--- チェック制約を設定する場合
CREATE TABLE テーブル名 (カラム名 データ型 CHECK (条件式));
CREATE TABLE テーブル名 (カラム名 データ型[, カラム名 データ型...], [CONSTRAINT 制約名] CHECK (条件式));
```
## インデックス
- WHERE句などの条件でカラムが指定されていない場合、インデックスは使用されない。
## シーケンス(SEQUENCE)
自動で連番を振ってくれる機能。プラス・マイナス両方増減が可能。**初期値は1。**
```sql
CREATE SEQUENCE シーケンス名 [オプション];
```
【主なオプション】
・INCREMENT [BY] 増減値(デフォルト：1)
シーケンスをいくつずつ増やすかを指定します。
マイナス値（いくつずつ減らすか）も指定可能です。
・MINVALUE 最小値
シーケンスの最小値を指定します。
指定しない場合のデフォルト値は昇順であれば1、降順であれば-(263-1)です。
・MAXVALUE 最大値
シーケンスの最大値を指定します。
指定しない場合のデフォルト値は昇順であれば263-1、降順であれば-1です。
・START [WITH] 初期値(デフォルト：1)
シーケンスをいくつから始めるかを指定します。
マイナス値も指定可能です。
・CACHE キャッシュ数
メモリに格納できるシーケンス番号の量を指定します。
検索処理の高速化に使用できます。
**※歯抜けすることがあるので気をつける。** 対策として、キャッシュサイズを1に設定する。
・CYCLE
シーケンスが上限または下限に到達した後、初期値に戻るよう指定します。
・NO CYCLE
シーケンスが上限または下限に到達した場合、エラーになるように指定します。
**シーケンス操作関数**
| 関数 | 説明 |
| ----------------------- | -------------------------------------------- |
| nextval('シーケンス名') | シーケンスを進めて、次に採番される値を返す。 |
| currval('シーケンス名')|対象のシーケンスの現在の値を返す。|
| setval('シーケンス名', 'シーケンス値')|次に採番される値を返す。|
```sql
CREATE SEQUENCE sample_sequence
INCREMENT BY 1
START 1
CYCLE
CACHE 1
;
CREATE SEQUENCE
select * from sample_sequence;
last_value | log_cnt | is_called
-- ------------+---------+-----------
1 | 0 | f
(1 row)
```
## パーティショニング
テーブルのデータを複数のまとまりに分割する機能。1つのテーブルを物理的に分割するため、データへの処理パフォーマンスを向上させることができる。
パーティショニングには3種類存在する。
■リストパーティショニング
カラムの値によって分割する。
■レンジパーティショニング
値の範囲によって分割する。
■ハッシュパーティショニング
均等に分割する。INSERTが多い処理で、分散させることで競合を防ぐために使用したりする。
■CREATE TABLE PARTITION {BY | OF}
パーティションを作成するコマンドです。
書式は以下の通りです。
・リスト・パーティションを作成する場合
[親テーブル] CREATE TABLE 親テーブル名 (親テーブル定義) PARTITION BY LIST (キーとなるカラム名);
[子テーブル] CREATE TABLE 子テーブル名 PARTITION OF 親テーブル名 FOR VALUES IN (分割するカラムの値);
・レンジ・パーティションを作成する場合
[親テーブル] CREATE TABLE 親テーブル名 (親テーブル定義) PARTITION BY RANGE (キーとなるカラム名);
[子テーブル] CREATE TABLE 子テーブル名 PARTITION OF 親テーブル名 FOR VALUES FROM (分割するカラムの開始値) TO (分割するカラムの終了値);
・ハッシュ・パーティションを作成する場合
[親テーブル] CREATE TABLE 親テーブル名 (親テーブル定義) PARTITION BY HASH (キーとなるカラム名);
[子テーブル] CREATE TABLE 子テーブル名 PARTITION OF 親テーブル名 FOR VALUES WITH (MODULUS 分割する数, REMAINDER 分割する数より小さな整数値);
■ALTER TABLE {ATTACH | DETACH} PARTITION
パーティションを切り離すまたは組み込むコマンドです。
書式は以下の通りです。
・パーティションを組み込む場合
ALTER TABLE 親テーブル名 ATTACH PARTITION 子テーブル名 FOR VALUES IN ('分割するカラムの値');
ALTER TABLE 親テーブル名 ATTACH PARTITION 子テーブル名 FOR VALUES FROM (分割するカラムの開始値) TO (分割するカラムの終了値);
ALTER TABLE 親テーブル名 ATTACH PARTITION 子テーブル名 FOR VALUES WITH (MODULUS 分割する数, REMAINDER 分割する数より小さな整数値);
・パーティションを切り離す場合
ALTER TABLE 親テーブル名 DETACH PARTITION 子テーブル名;
## SELECT
- 複数テーブルをSELECTする時、同じカラム名が存在する場合テーブルも指定する必要がある。被らない場合は、テーブル名の指定は不要。
## VIEW
SELECTの結果をテーブルのように定義できる機能(仮想テーブル)。元テーブルに対する参照権限がなくても、VIWEに対する参照権限さえあれば、SELECTできる。
通常、VIEWを更新したい場合はRULEが必要だが、下記の条件を満たす場合のみ更新処理が可能。
- 複数テーブルの結合を行っていない
- 集約関数を使用していない
■CREATE VIEW
ビューを作成するコマンドです。実行には、ビューの元となるテーブルに対してSELECT権限が必要です。
書式は以下の通りです。
CREATE [OR REPLACE] VIEW ビュー名 [(カラム名)] AS 検索処理;
CREATE VIEWでOR REPLACEオプションを指定すると、すでに作成済みのビューを置き換えられます。
```sql
CREATE VIEW sample_view AS SELECT * FROM sample;
```
■CREATE RULE
更新処理ができないVIEWに対して、RULEを発行することで更新することが可能になる。
```sql
CREATE RULE sample_view_rule AS ON INSERT TO sample_view DO INSTEAD(
INSERT INTO ~;
INSERT INTO ~;
);
```
## スキーマ
search_pathには複数のスキーマを指定することができる。
`SET search_path TO USER1, USER2, USER3`
それぞれのスキーマで共通のテーブルを持っていた場合、上記でスキーマを複数選択した場合、左から順にテーブルを検索し、該当のデータを返す。そのため、USER2、USER3のスキーマからは検索されない。
## 結合
■test_aテーブル
|id|name|post_id|
| -------- | -------- | -------- |
|1|name1|1|
|2|name2|1|
|3|name3|3|
|4|name4|4|
|5|name5|5|
■test_bテーブル
| id | post_no |
| --- | ------- |
| 1 | 123456 |
| 2 | 123457 |
| 3 | 123458 |
| 4 | 123459 |
### INNER JOIN
結合キーの値が**等しい**データを取得する。
```sql
SELECT * FROM test_a a INNER JOIN test_b b ON a.post_id = b.id ORDER BY a.id;
id | name | post_id | id | post_no
----+-------+---------+----+---------
1 | name1 | 1 | 1 | 123456
2 | name2 | 1 | 1 | 123456
3 | name3 | 3 | 3 | 123458
4 | name4 | 4 | 4 | 123459
(4 rows)
```
### OUTER JOIN
結合キーの条件に合致しなかったデータも取得する。
LEFT: 左側のテーブルから条件に一致しないデータを取得。
RIGHT: 右側のテーブルから条件に一致しないデータを取得。
FULL: 両方のテーブルから条件に一致しないデータを取得。
```sql
SELECT * FROM test_a a LEFT JOIN test_b b ON a.post_id = b.id ORDER BY a.id;
id | name | post_id | id | post_no
----+-------+---------+----+---------
1 | name1 | 1 | 1 | 123456
2 | name2 | 1 | 1 | 123456
3 | name3 | 3 | 3 | 123458
4 | name4 | 4 | 4 | 123459
5 | name5 | 5 | |
(5 rows)
SELECT * FROM test_a a RIGHT JOIN test_b b ON a.post_id = b.id ORDER BY a.id;
id | name | post_id | id | post_no
----+-------+---------+----+---------
1 | name1 | 1 | 1 | 123456
2 | name2 | 1 | 1 | 123456
3 | name3 | 3 | 3 | 123458
4 | name4 | 4 | 4 | 123459
| | | 2 | 123457
(5 rows)
```
Sign in with Wallet
Connect another wallet