概要
PostgreSQLを使用する機会があったので勉強を始めてみました。
大事そうなところ、かつ忘れてしまいそうなところに絞ってメモしようと思います。
参照文献
- オープンソースデータベース標準教科書 Ver.2.0.1
- PostgreSQL 14.5文書
- PostgreWeb
- PostgreSQLの使い方
- OSS-DB 技術解説無料セミナー
- GCon 2014 Tokyo【D1】PostgreSQL バックアップ入門（佐藤 千佳）
定数
文字列定数
シングルクォーテーションで囲んで記述する。
'文字列'
文字列中にシングルクォーテーションがある場合には、シングルクォーテーションを2個連続して記述する。\マークはそのまま文字列として扱われる。
'I''ll be back.'
PostgreSQL 14ではバックスラッシュでエスケープすることもできるが、今後は使用できなくなる予定らしいので要注意。
単一引用符をバックスラッシュでエスケープすること（\'）ができます。 しかし、今後のPostgreSQLのバージョンではこれはできなくなる予定です。 ですので、バックスラッシュを使用するアプリケーションを上述の標準に準拠するように変更しなければなりません。
エスケープ文字構文
エスケープ文字構文を使用することで特殊な文字も入力できる。 エスケープ文字構文では、文字列をシングルクォーテーションで囲み、先頭にEまたはeを記述する。
E'\041' あるいは e'\041'
ビット文字列、16進文字列
ビット文字列
B'01'やb'011110'16進文字列
X'3F'やx'A37E'
データ型
文字列型
varchar型、char型、text型がある。
varchar型は可変長文字列型とchar型は固定長文字列型である。
text型は、文字数に上限のない可変長の文字列型。便利だが、ANSI SQL標準外なので要注意。
浮動小数点型
numeric、real、double precisionがある（real型って由来はなんなんだろう、、、）
numeric(8, 2)で少数点以下2桁、整数部が6桁の値を格納できる。整数部の桁数を超えるとエラーとなる。
real型は4バイトの小数点以下6桁の精度、double precision型は8バイトの小数点以下15桁の精度である。
serial型
テーブル作成時にserial型のカラムを作り、インサート文でそのカラムに値を指定せずにレコードを挿入すると、自動的にシリアルな整数を挿入できる。
裏側ではシーケンスが動いているらしい。
serial型のカラムに任意の整数値を挿入できるが、serial型のカラムはそれを検知しないので、重複した整数値が払い出される。
serial型が4バイトで 1 から 2147483647 まで扱えるのに対し、bigserial型は8バイトで 1 から 9223372036854775807まで扱うことができる。
日付・時刻データ型
date型、timestamp型、time型がある。
date型は日付のみ格納できる。時刻データ部分は切り捨てられる。Oracleのdate型とは異なるので要注意。
例：2018-01-23
timestamp型は日付と時刻を格納できる。秒数は小数点以下6桁まで扱うことができる。
例：2018-01-23 12:34:56.526066
timeがたは時刻のみ格納する。日付データは持たない。
例：12:34:56.526066
キャスト
CAST関数を使用する方法と::を使用する方法がある。
以下のどちらのケースでもinteger型の123になる。
SELECT CAST('123' AS integer);
SELECT '123'::interger;
データベースクラスタの作成、初期化
データベースクラスタ ＝ ディレクトリ（テーブルファイルとかインデックスファイルとか設定ファイルとか管理ファイルが格納される）
initdbコマンドでデータベースクラスタを作成できる。
initdb --no-locale --encoding=UTF8 --pgdata=/var/lib/pgsql/14/data
エンコーディングはUTF8を指定しとけばオーケー。
localeというのは地域の言語や文化に応じた処理をしてくれるOSの仕組みのこと。例えば、日付や通貨の表示処理、文字列のソート順序処理など。PostgreSQLではロケール使用は非推奨です。ロケール機能には統一した仕様が存在しないため。
--pgdataにはデータベースクラスタを作成するディレクトリを指定する。環境変数 $PGDATAに指定することもできる。
$PGDATA/postgresql.confに設定をしておく。
データベースクラスタはpg_ctlコマンドで起動できる。どのデータベースクラスタを利用するかは--pgdataフラグ、または環境変数 $PGDATAに設定しておく。pg_ctl statusでデータベースクラスタの起動状態を確認できる。
pg_ctl start --pgdata=/var/lib/pgsql/14/data pg_ctl status
データベースクラスタを停止する際も同様にデータベースクラスタを指定する。
pg_ctl stop --pgdata=/var/lib/pgsql/14/data
デフォルトのデータベース
Postgreのデータベースにはデフォルトで、postgresとtemplate0、template1がある。
template0、template1はデータベースのテンプレートである。
create database文でデータベースを作成する際、デフォルトでtemplate1をコピーしてデータベースを作成する。template1は書込み可能なので、あらかじめtemplate1に設定を登録しておくと便利。
pgbench
PostgreSQＬに同梱されているベンチマークツール。
TBC-Bという銀行口座、支店、窓口担当者の業務をモデル化しているベンチマークシナリオを用いている。
pgbenchコマンドでデータベースにベンチマークテーブルを作成できる。
pgbench --initialize --scale=30 <データベース名>
--initializeはベンチマークテーブルの初期化を意味する。
--scaleはベンチマークテーブルの規模を表す。--scale=1でデータベースサイズが15MBほどになる。
