PostgreSQL
- PostgreSQLについての自分用メモです。
- 普段使わないようなクエリは忘れてしまうのでまとめています。
- マニュアルではないので、オプションなどは自分がつかうもののみ記入しています。
データベース
データベース操作
データベース一覧
- SQL
sql {.line-numbers}
SELECT * FROM pg_database;
- PSQL
\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アドレス |