PostgreSQLの備忘録

# PostgreSQL

- PostgreSQLについての自分用メモです。
- 普段使わないようなクエリは忘れてしまうのでまとめています。
- マニュアルではないので、オプションなどは自分がつかうもののみ記入しています。

## データベース

### データベース操作

#### データベース一覧

- SQL

```sql {.line-numbers}
SELECT * FROM pg_database;
```

- PSQL

```sql
\l
```

#### データベース作成

```sql {.line-numbers}
CREATE DATABASE {データベース名};
```

#### データベース削除

```sql {.line-numbers}
DROP DATABASE {データベース名};
```

#### サーチパス（デフォルトスキーマ）

```sql {.line-numbers}
SHOW search_path;
```

- デフォルトスキーマを返す

#### サーチパス(デフォルトスキーマ)置き換え

```sql {.line-numbers}
SET search_path TO {スキーマ名};
```

#### データベースの容量

```sql {.line-numbers}
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
```

### バックアップ＆リストア(DOSコマンド)

#### バックアップ(プレーン)

- dosコマンド

```bash {.line-numbers}
pg_dump -h {ホスト名 or IP} -p {ポート番号} -F p -U {ユーザ名} {DB名} > {ファイル名}
{パスワード}
```

#### バックアップ(カスタム)

- dosコマンド

```bash {.line-numbers}
pg_dump -h {ホスト名 or IP} -p {ポート番号} -F c -U {ユーザ名} {DB名} > {ファイル名}
{パスワード}
```

#### リストア(プレーン)

- dosコマンド

```bash {.line-numbers}
psql -h {ホスト名 or IP} -p {ポート番号} -U {ユーザ名} {DB名} < {ファイル名}
{パスワード}
```

#### リストア(カスタム圧縮)

- dosコマンド

```bash {.line-numbers}
pg_restore -h {ホスト名 or IP} -p {ポート番号} -U {ユーザ名} -d {DB名} {ファイル名}
{パスワード}
```

## テーブル設計

### スキーマ

#### スキーマ一覧

```sql {.line-numbers}
SELECT * FROM pg_namespace;
```

#### スキーマ作成

```sql {.line-numbers}
CREATE SCHEMA {スキーマ名};
```

#### スキーマ削除

```sql {.line-numbers}
DROP SCHEMA {スキーマ名} [CASCADE];
```

- CASCADE ・・・ スキーマ内にオブジェクトがあっても削除する

### テーブル操作

#### テーブル一覧

```sql {.line-numbers}
SELECT * FROM pg_tables;
```

#### テーブル作成

```sql {.line-numbers}
CREATE TABLE [IF NOT EXISTS] {スキーマ名}.{テーブル名} (
    {カラム名} {型} [{NOT NULL / NULL}]
    [, ...]
    CONSTRAINT {プライマリキー名} PRIMARY KEY ({フィールド名}[, ...])
)
```

- IF NOT EXISTS ・・・ テーブルが存在していないとき
- NOT NULL ・・・ NULLを許可しない
- NULL ・・・ NULLを許可する

#### テーブル削除

```sql {.line-numbers}
DROP TABLE [IF EXISTS] {スキーマ名}.{テーブル名} [CASCADE | RESTRICT];
```

- IF EXISTS ・・・ テーブルが存在したら
- CADCADE ・・・ 参照されていても強制削除（参照が削除される）
- RESTRICT ・・・ 【デフォルト】参照がある場合は削除できない

### プライマリキー

#### プライマリキー一覧

```sql {.line-numbers}
SELECT * FROM information_schema.table_constraints;
```

#### プライマリキー追加

```sql {.line-numbers}
ALTER TABLE {スキーマ名}.{テーブル名} ADD CONSTRAINT {プライマリキー名}
 PRIMARY KEY ({フィールド名} [, ...]);
```

#### プライマリキー削除

```sql {.line-numbers}
ALTER TABLE {スキーマ名}.{テーブル名} DROP CONSTRAINT {プライマリキー名};
--NOT NULL属性は消えないので、以下のようにして制約を消す
--ALTER TABLE {スキーマ名}.{テーブル名} ALTER COLUMN {フィールド名} DROP NOT NULL;
```

#### プライマリキーフィールド一覧

```sql {.line-numbers}
SELECT ccu.column_name as COLUMN_NAME
 FROM information_schema.table_constraints tc
 INNER JOIN information_schema.constraint_column_usage ccu
 ON (
       tc.table_catalog  = ccu.table_catalog
   and tc.table_schema   = ccu.table_schema
   and tc.table_name     = ccu.table_name
   and tc.constraint_name= ccu.constraint_name
 )
 WHERE tc.table_catalog  = '{データベース名}'
   and tc.table_schema   = '{スキーマ名}'
   and tc.table_name     = '{テーブル名}'
   and tc.constraint_type= 'PRIMARY KEY'
```

### インデックス

#### インデックス一覧

```sql {.line-numbers}
SELECT * FROM pg_indexes;
```

#### インデックスの追加

```sql {.line-numbers}
CREATE INDEX {テーブル名}_idx
 ON {スキーマ名}.{テーブル名} ( {フィールド名1}, {フィールド名2},,, );
```

#### インデックスの削除

```sql {.line-numbers}
DROP INDEX IF EXISTS {インデックス名};
```

### 列

#### 列一覧

```sql {.line-numbers}
SELECT * FROM information_schema.columns;
```

#### 列の追加

```sql {.line-numbers}
ALTER TABLE {スキーマ}.{テーブル名} ADD COLUMN {カラム名称} {型};
```

#### 列の削除

```sql {.line-numbers}
ALTER TABLE {スキーマ}.{テーブル名} DROP COLUMN {カラム名称};
```

#### 列の定義変更

```sql {.line-numbers}
ALTER TABLE {スキーマ}.{テーブル名} ALTER COLUMN {フィールド名} TYPE {型};
```

### SERIAL

#### SERIAL一覧

```sql {.line-numbers}
SELECT * FROM pg_class c WHERE c.relkind='S';
```

#### SERIAL更新

```sql {.line-numbers}
SELECT SETVAL ('[スキーマ名.]シーケンス名', '番号');
```

- 2022/09/07追記：スキーマ名記述

## 管理

### ユーザ

- **ユーザはロールの別名扱いで削除予定**

#### ユーザ一覧

```sql {.line-numbers}
SELECT * FROM pg_user;
```

#### ユーザ作成

```sql {.line-numbers}
CREATE USER {ユーザ名};
```

#### ユーザ削除

```sql {.line-numbers}
DROP USER {ユーザ名};
```

#### ユーザにパスワードを設定

```sql {.line-numbers}
ALTER USER {ユーザ名} WITH PASSWORD '{パスワード}';
```

### ロール

#### ロール一覧

```sql {.line-numbers}
SELECT * FROM pg_roles;
```

#### ロール作成

```sql {.line-numbers}
CREATE ROLE {ロール名};
```

## 型

### 型一覧

#### 数値

| 型名             | 格納サイズ | 説明                 | 範囲                                        |
| ---------------- | ---------- | -------------------- | ------------------------------------------- |
| smallint         | 2バイト    | 狭範囲の整数         | -32768から+32767                            |
| integer          | 4バイト    | 典型的に使用する整数 | -2147483648から+2147483647                  |
| bigint           | 8バイト    | 広範囲整数           | -9223372036854775808から9223372036854775807 |
| decimal          | 可変長     | ユーザ指定精度、正確 | 小数点前までは131072桁、小数点以降は16383桁 |
| numeric          | 可変長     | ユーザ指定精度、正確 | 小数点前までは131072桁、小数点以降は16383桁 |
| real             | 4バイト    | 可変精度、不正確     | 6桁精度                                     |
| double precision | 8バイト    | 可変精度、不正確     | 15桁精度                                    |
| smallserial      | 2バイト    | 狭範囲自動整数       | 1から32767                                  |
| serial           | 4バイト    | 自動増分整数         | 1から2147483647                             |
| bigserial        | 8バイト    | 広範囲自動増分整数   | 1から9223372036854775807                    |

#### 通貨

| 型名  | 格納サイズ | 説明     | 範囲                                             |
| ----- | ---------- | -------- | ------------------------------------------------ |
| money | 8バイト    | 貨幣金額 | -92233720368547758.08 から +92233720368547758.07 |

#### 文字列

| 型名                             | 説明                   |
| -------------------------------- | ---------------------- |
| character varying(n)、varchar(n) | 上限付き可変長         |
| character(n)、char(n)            | 空白で埋められた固定長 |
| text                             | 制限なし可変長         |

#### バイナリ型

| 型名  | 格納サイズ                             | 説明               |
| ----- | -------------------------------------- | ------------------ |
| bytea | 1または4バイトと実際のバイナリ列の長さ | 可変長のバイナリ列 |

#### 日付/時刻データ型

| 型名                                    | 格納サイズ | 説明                         | 最遠の過去    | 最遠の未来    | 精度       |
| --------------------------------------- | ---------- | ---------------------------- | ------------- | ------------- | ---------- |
| timestamp [ (p) ] [ without time zone ] | 8 バイト   | 日付と時刻両方（時間帯なし） | 4713 BC       | 294276 AD     | 1μ秒、14桁 |
| timestamp [ (p) ] with time zone        | 8バイト    | 日付と時刻両方、時間帯付き   | 4713 BC       | 294276 AD     | 1μ秒、14桁 |
| date                                    | 4バイト    | 日付（時刻なし）             | 4713 BC       | 5874897 AD    | 1日        |
| time [ (p) ] [ without time zone ]      | 8バイト    | 時刻（日付なし）             | 00:00:00      | 24:00:00      | 1μ秒、14桁 |
| time [ (p) ] with time zone             | 12バイト   | その日の時刻のみ、時間帯付き | 00:00:00+1459 | 24:00:00-1459 | 1μ秒、14桁 |
| interval [ fields ] [ (p) ]             | 12バイト   | 時間間隔                     | -178000000年  | 178000000年   | 1μ秒、14桁 |

#### 論理値データ型

| 名前    | 格納サイズ | 説明             |
| ------- | ---------- | ---------------- |
| boolean | 1バイト    | 真または偽の状態 |

#### 幾何データ型

| 型名    | 格納サイズ   | 表現                                     | 説明                    |
| ------- | ------------ | ---------------------------------------- | ----------------------- |
| point   | 16バイト     | 平面における座標点                       | (x,y)                   |
| line    | 32バイト     | 無限の直線（完全には実装されていません） | ((x1,y1),(x2,y2))       |
| lseg    | 32バイト     | 有限の線分                               | ((x1,y1),(x2,y2))       |
| box     | 32バイト     | 矩形                                     | ((x1,y1),(x2,y2))       |
| path    | 16+16nバイト | 閉経路（多角形に類似）                   | ((x1,y1),...)           |
| path    | 16+16nバイト | 開経路                                   | [(x1,y1),...]           |
| polygon | 40+16nバイト | 多角形（閉経路に類似）                   | ((x1,y1),...)           |
| circle  | 24バイト     | 円                                       | <(x,y),r>（中心と半径） |

#### ネットワークアドレス型

| 名前    | 格納サイズ        | 説明                                       |
| ------- | ----------------- | ------------------------------------------ |
| cidr    | 7もしくは19バイト | IPv4、およびIPv6ネットワーク               |
| inet    | 7もしくは19バイト | IPv4もしくはIPv6ホスト、およびネットワーク |
| macaddr | 6バイト           | MACアドレス                                |