---
# System prepended metadata

title: Ping-TにてOSSDBの勉強
tags: [postgresql]

---

# 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つのテーブルを物理的に分割するため、データへの処理パフォーマンスを向上させることができる。

![](https://i.imgur.com/CHqxjne.png)

パーティショニングには3種類存在する。
■リストパーティショニング
カラムの値によって分割する。
![](https://i.imgur.com/WQNDdII.png)


■レンジパーティショニング
値の範囲によって分割する。
![](https://i.imgur.com/6yNv4z8.png)


■ハッシュパーティショニング
均等に分割する。INSERTが多い処理で、分散させることで競合を防ぐために使用したりする。
![](https://i.imgur.com/yCmopi2.png)

■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)

```