こんにちは。サイオステクノロジー OSS サポート担当 T です。
普段気にせず使っている PostgreSQL の文字列ですが、いろいろな種類や機能があります。今回は文字列型について、あまり細かくなりすぎない程度にまとめて紹介します。基本的な部分ですが、案外見落としていることも多いです。
PostgreSQL の文字列型
PostgreSQL の文字列型には char, var char, text の3種類があります。
- char(n), character(n) – n 文字の固定長文字列型で、短い場合はスペースで埋められる。
- varchar(n), character varying(n) – n 文字の可変長文字列。
- text – 長さ制限がない文字列。
char/varchar の n 文字は n バイトではなく、利用している文字エンコーディングの n 文字分のデータが保存できます。
char(n), character(n) はスペースで埋められますが、LIKE などで比較する場合に考慮されません。PostgreSQL は文字列型の違いによる性能の差はほとんどありません。しかし、char(n) 型の場合はスペースを埋める処理の為、若干遅くなり、その分の保存領域も必要になります。
text 型は長さの制限を記述できませんが、PostgreSQL のフィールド最大長は 1GBです。これ以上の大きさのデータは保存できません。
この他に PostgreSQL 内部で利用する為の特別な文字列型が定義されています。
- char – char(1) とは異なる。単一文字のデータ型。
- name – テーブル名、フィールド名などに利用される 64バイトのデータ型。
name 型は 64バイトですが最後の NULL 文字分も必要であるため、実際に利用できるのは 63バイトです。この大きさはコンパイル時に変更できます。SQL 標準の場合、128バイトまでですが 128バイトにした際のパフォーマンスへの影響が無視できない為、64バイトに設定されています。英数字でテーブル名、フィールド名を定義する場合にはあまり問題になりません。しかし、日本語かつ UTF-8 エンコーディングの場合、一文字に 3バイト必要であるため比較的簡単 (21文字) に最大長に達するので注意が必要です。
user@[local] ~=> CREATE TABLE "日本語の長い名前のテーブル名はテーブル名の長さ制限に" (i int);
NOTICE: 42622: identifier "日本語の長い名前のテーブル名はテーブル名の長さ制限に" will be truncated to "日本語の長い名前のテーブル名はテーブル名の"
LOCATION: truncate_identifier, scansup.c:195
CREATE TABLE
時間: 199.787 ms
user@[local] ~=> \d
リレーションの一覧
スキーマ | 名前 | 型 | 所有者
----------+--------------------------------------------+----------+---------
public | htest | テーブル | user
public | 日本語の長い名前のテーブル名はテーブル名の | テーブル | user
(2 行)
文字列型ではありませんが、ビット文字列型も文字列型と同様に記述できます。
-
- bit(n) – 厳格に n ビットの “0”, “1” のビット文字列を定義する。
- varbit(n), bit varying(n) - 可変長の n ビット文字列を定義する。
bit(n) は必ず n ビットの長さで保存され、長い場合、短い場合はエラーが発生する。短い文字列を明示的にキャストした場合は “0” で埋められる。bit varying(n) は、n ビットより短い文字列の場合そのまま保存され、長い場合はエラーとなる。明示的にキャストした場合は右側に丸められる。
user@[local] ~=> CREATE TABLE test (a BIT(3), b BIT VARYING(5)); CREATE TABLE 時間: 18.611 ms user@[local] ~=> INSERT INTO test VALUES (B'101', B'00'); INSERT 0 1 時間: 193.957 ms user@[local] ~=> INSERT INTO test VALUES (B'10', B'101'); ERROR: 22026: bit string length 2 does not match type bit(3) LOCATION: bit, varbit.c:378 時間: 0.222 ms user@[local] ~=> INSERT INTO test VALUES (B'10'::bit(3), B'101'); INSERT 0 1 時間: 196.313 ms user@[local] ~=> INSERT INTO test VALUES (B'1010'::bit(3), B'101010101'::varbit(5)); INSERT 0 1 時間: 193.487 ms user@[local] ~=> SELECT * FROM test; a | b -----+------- 101 | 00 100 | 101 101 | 10101 (3 行) 時間: 0.516 ms
PostgreSQL 文字列型の表記
文字リテラルとして表記される文字列型データ (PostgreSQL 日本語マニュアルでは文字列型定数と記載されている) には幾つかの種類があります。
- ‘文字列’ – シングルクオートで囲まれた文字リテラル
- E’文字列’ – エスケープシーケンスをサポートした文字リテラル。e’文字列’ と表記しても同じ。
- U&’文字列’ – Unicode エスケープシーケンスをサポートする文字列。u&’文字列’ と記載しても同じ。
- $$文字列$$ – ストアドプロシージャなどで利用すると便利な表記方式。$クオート名$文字列$クオート名$ (例：$q$文字列$q$) と記述もできる。
- B’ビット文字列’ – バイナリを記述する文字列。0 と 1 のみ記述できる。b’ビット文字列’ と表記しても同じ。
- X’ビット文字列’ – バイナリを記述する文字列。0-9a-f の 16進数で記述できる。x’ビット文字列’ と表記しても同じ。
‘文字列’、E’文字列’、U&’文字列’ はクオート文字をエスケープしなければならない場合があります。その場合、’ (シングルクオート) でエスケープします。
user@[local] ~=> SELECT 'abc''def'; ?column? ---------- abc'def (1 行) 時間: 0.360 ms user@[local] ~=> SELECT E'abc''def'; ?column? ---------- abc'def (1 行) 時間: 0.384 ms user@[local] ~=> SELECT U&'abc''def'; ?column? ---------- abc'def (1 行) 時間: 0.387 ms
■E’文字列’
E’文字列’ は SQL 標準の拡張で、以下のエスケープシーケンスをサポートしています。
バックスラッシュエスケープシーケンス 解釈 \b 後退 \f 改ページ \n 改行 \r 復帰 \t タブ \o, \oo, \ooo (o = 0 - 7) 8進数バイト値 \xh, \xhh (h = 0 - 9, A - F) 16進数バイト値 \uxxxx, \Uxxxxxxxx (x = 0 - 9, A - F) 16 もしくは 32ビットの 16進数 Unicode 文字コード番号
例：
user@[local] ~=> SELECT 'abc\nxyz'; ?column? ---------- abc\nxyz (1 行) 時間: 0.377 ms user@[local] ~=> SELECT E'abc\nxyz'; ?column? ---------- abc + xyz (1 行) 時間: 0.300 ms
■U&’文字列’
U&’文字列’ も Unicode エスケープシーケンスで記述できますが、エスケープの方法が異なります。この記述方法によるユニコードエスケープ構文は設定パラメータ standard_conforming_strings が有効なときのみ動作します。PostgreSQL 9.1 以降の場合、この設定はデフォルトで有効です。
\xxxx - 16進数 4桁の Unicode 文字コード番号 \+xxxxxx - 16進数 6桁の Unicode 文字コード番号
例：
user@[local] ~=> SELECT U&'d\0061t\+000061'; ?column? ---------- data (1 行) 時間: 0.238 ms
U&’文字列’ の場合、LIKE 句と同様に UESCAPE 句を用いてエスケープ文字を指定できます。
例： U&’d!0061t!+000061′ UESCAPE ‘!’
UESCAPE 句は U&’文字列’ にのみ利用できます。
user@[local] ~=> SELECT U&'d\0061t\+000061' UESCAPE '!';
?column?
-----------------
d\0061t\+000061
(1 行)
時間: 0.155 ms
user@[local] ~=> SELECT E'abc\nxyz' UESCAPE '!';
ERROR: 42601: syntax error at or near "'!'"
行 1: SELECT E'abc\nxyz' UESCAPE '!';
^
LOCATION: scanner_yyerror, scan.l:1081
時間: 0.131 ms
user@[local] ~=>
エスケープ文字をエスケープするには同じ文字を二回繰り返します。
■$$文字列$$
「ドル引用符付け」と呼ばれる PostgreSQL の機能です。ストアドプロシージャなどで便利ですが、普通の引用符付き文字列を同じように利用できます。
user@[local] ~=> SELECT $$abcxyz$$; ?column? ---------- abcxyz (1 行) 時間: 0.384 ms user@[local] ~=> SELECT $quotename$abcxyz$quotename$; ?column? ---------- abcxyz (1 行) 時間: 0.433 ms
別の引用符名を利用すると「ドル引用符付け」はネストも可能です。関数を定義する場合に、通常の \ によるエスケープでは「関数定義の際に必要な文字列のエスケープ」と「ストアドプロシージャを実行する場合に必要なエスケープ」が必要となり、’\’ を繰り返し書かなければなりません。「ドル引用符付け」を入れ子にすると解りやすく記述できます。
user@[local] ~=> SELECT
user-> $function$
user$> BEGIN
user$> RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
user$> END;
user$> $function$
user-> ;
?column?
---------------------------------------
+
BEGIN +
RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);+
END; +
(1 行)
時間: 0.389 ms
■ビット文字列
B’ビット文字列’、X’ビット文字列’ が利用できます。bit(n), varbit(n) で定義したカラムにデータを保存できます。16進数形式の X’ビット文字列’ を利用した場合も、B’ビット文字列’ を利用した時と同様にカラムに対して大きすぎる場合、小さすぎる場合にエラーが発生します。
user@[local] ~=> INSERT INTO test VALUES (X'FF'::bit(3), X'FF'::varbit(5)); INSERT 0 1 時間: 195.702 ms user@[local] ~=> INSERT INTO test VALUES (X'FF', X'FF'::varbit(5)); ERROR: 22026: bit string length 8 does not match type bit(3) LOCATION: bit, varbit.c:378 時間: 0.303 ms user@[local] ~=> SELECT * FROM test; a | b -----+------- 111 | 11111 (1 行) 時間: 0.256 ms
バイナリ型
bytea 型は text 型と似ています。最大 1GB までのバイナリデータを保存できます。データ自体は 16進数文字列 (9.0 より古い PostgreSQL は別の形式を利用) として表現できます。”\x” で始まる 16進数データがバイナリとして保存されます。
user@[local] ~=> SELECT '\xDEADBEEF'; ?column? ------------ \xDEADBEEF (1 行) 時間: 0.396 ms
PostgreSQL のクライアントライブラリにはエスケープとアンエスケープ処理を行う PQescapeByteaConn, PQunescapeBytea が用意されています。プログラムなどから利用する場合、これらの API を利用したエスケープ関数が提供されていると思います。
識別子
識別子はデータ型ではありませんが、識別子 (テーブル名、フィールド名など) も文字列型と同様に定義できます。英数字のみの識別子はクオート無しで利用できますが、それ以外の場合は ” (ダブルクオート) でクオートします。
- “識別子”
- U&”識別子”
の形式が利用できます。テーブル名、フィールド名などに空白文字など意味がある文字を利用する場合にはクオートが必要です。予約語もクオートすれば識別子として利用できます。識別子は name 型を利用しているので最大の長さは 63バイトに制限されます。
user@[local] ~=> SELECT a as U&"d\0061t\+000061" FROM test; data ------ 111 (1 行) 時間: 0.516 ms
この例では a カラムを “data” として表示しています。
user@[local] ~=> SELECT a AS "SELECT" FROM test; SELECT -------- 111 (1 行) 時間: 0.493 ms
この例では予約語である SELECT をクオートして識別子として利用しています。
今の PostgreSQL は日本語名の識別子もクオート無しで利用できます。長すぎる識別子はまるめられ、その結果無効な文字エンコーディングになるとエラーになります。
user@[local] ~=> SELECT a AS "日本語の長いエイリアスの場合、64文字制限は" FROM test; 日本語の長いエイリアスの場合、64文字制限は -------------------------------------------- 111 (1 行) 時間: 0.557 ms user@[local] ~=> SELECT a AS "日本語の長いエイリアスの場合、64文字制限はある" FROM test; ERROR: 22021: invalid byte sequence for encoding "UTF8": 0xe3 0x22 LOCATION: report_invalid_encoding, wchar.c:2017 時間: 0.309 ms user@[local] ~=> SELECT a AS 日本語のカラム名もOK FROM test; 日本語のカラム名もok ---------------------- 111 (1 行) 時間: 1.153 ms
識別子はクオートしないと小文字に変換されてしまいますが、クオートすれば大文字小文字を維持できます。
user@[local] ~=> SELECT a AS "ABCdef" FROM test; ABCdef -------- 111 (1 行) 時間: 0.525 ms user@[local] ~=> SELECT a AS ABCdef FROM test; abcdef -------- 111 (1 行) 時間: 0.357 ms
大文字小文字を区別した定義を行うとクエリの際にもクオートが必要になります。
user@[local] ~=> create table "TestTable" ("Int" int);
CREATE TABLE
時間: 22.874 ms
user@[local] ~=> \d "TestTable"
テーブル "public.TestTable"
列 | 型 | 修飾語
-----+---------+--------
Int | integer |
user@[local] ~=> SELECT "Int" FROM "TestTable";
Int
-----
(0 行)
時間: 0.585 ms
■識別子のエスケープ
識別子も文字列型と同様にエスケープします。エスケープには ” (ダブルクオート) を利用します。
user@[local] ~=> SELECT a as "abc""def""hig" FROM test; abc"def"hig ------------- 111 (1 行) 時間: 0.473 ms
まとめ
PostgreSQL の文字列に関する情報を簡単にまとめてみました。PostgreSQL マニュアルにはより詳しい情報が記載されています。簡単にまとめましたが普段は利用しないような機能でこんな使い方もあるのか？など、参考になれば幸いです。
参考：
https://www.postgresql.jp/document/9.5/html/datatype-character.html
https://www.postgresql.jp/document/9.5/html/datatype-binary.html
https://www.postgresql.jp/document/9.5/html/functions-string.html
https://www.postgresql.jp/document/9.5/html/libpq-exec.html
