PostgreSQL の文字列型についてまとめてみた

こんにちは。サイオステクノロジー 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文字) に最大長に達するので注意が必要です。

文字列型ではありませんが、ビット文字列型も文字列型と同様に記述できます。

  • bit(n) – 厳格に n ビットの “0”, “1” のビット文字列を定義する。
  • varbit(n), bit varying(n) - 可変長の n ビット文字列を定義する。
  • bit(n) は必ず n ビットの長さで保存され、長い場合、短い場合はエラーが発生する。短い文字列を明示的にキャストした場合は “0” で埋められる。bit varying(n) は、n ビットより短い文字列の場合そのまま保存され、長い場合はエラーとなる。明示的にキャストした場合は右側に丸められる。

PostgreSQL 文字列型の表記

文字リテラルとして表記される文字列型データ (PostgreSQL 日本語マニュアルでは文字列型定数と記載されている) には幾つかの種類があります。

  • ‘文字列’ – シングルクオートで囲まれた文字リテラル

  • E’文字列’ – エスケープシーケンスをサポートした文字リテラル。e’文字列’ と表記しても同じ。
  • U&’文字列’ – Unicode エスケープシーケンスをサポートする文字列。u&’文字列’ と記載しても同じ。
  • $$文字列$$ – ストアドプロシージャなどで利用すると便利な表記方式。$クオート名$文字列$クオート名$ (例:$q$文字列$q$) と記述もできる。
  • B’ビット文字列’ – バイナリを記述する文字列。0 と 1 のみ記述できる。b’ビット文字列’ と表記しても同じ。
  • X’ビット文字列’ – バイナリを記述する文字列。0-9a-f の 16進数で記述できる。x’ビット文字列’ と表記しても同じ。

‘文字列’、E’文字列’、U&’文字列’ はクオート文字をエスケープしなければならない場合があります。その場合、’ (シングルクオート) でエスケープします。

■E’文字列’

E’文字列’ は SQL 標準の拡張で、以下のエスケープシーケンスをサポートしています。

例:

■U&’文字列’

U&’文字列’ も Unicode エスケープシーケンスで記述できますが、エスケープの方法が異なります。この記述方法によるユニコードエスケープ構文は設定パラメータ standard_conforming_strings が有効なときのみ動作します。PostgreSQL 9.1 以降の場合、この設定はデフォルトで有効です。

例:

U&’文字列’ の場合、LIKE 句と同様に UESCAPE 句を用いてエスケープ文字を指定できます。

例: U&’d!0061t!+000061′ UESCAPE ‘!’

UESCAPE 句は U&’文字列’ にのみ利用できます。

エスケープ文字をエスケープするには同じ文字を二回繰り返します。

■$$文字列$$

「ドル引用符付け」と呼ばれる PostgreSQL の機能です。ストアドプロシージャなどで便利ですが、普通の引用符付き文字列を同じように利用できます。

別の引用符名を利用すると「ドル引用符付け」はネストも可能です。関数を定義する場合に、通常の \ によるエスケープでは「関数定義の際に必要な文字列のエスケープ」と「ストアドプロシージャを実行する場合に必要なエスケープ」が必要となり、’\’ を繰り返し書かなければなりません。「ドル引用符付け」を入れ子にすると解りやすく記述できます。

■ビット文字列

B’ビット文字列’、X’ビット文字列’ が利用できます。bit(n), varbit(n) で定義したカラムにデータを保存できます。16進数形式の X’ビット文字列’ を利用した場合も、B’ビット文字列’ を利用した時と同様にカラムに対して大きすぎる場合、小さすぎる場合にエラーが発生します。

バイナリ型

bytea 型は text 型と似ています。最大 1GB までのバイナリデータを保存できます。データ自体は 16進数文字列 (9.0 より古い PostgreSQL は別の形式を利用) として表現できます。”\x” で始まる 16進数データがバイナリとして保存されます。

PostgreSQL のクライアントライブラリにはエスケープとアンエスケープ処理を行う PQescapeByteaConn, PQunescapeBytea が用意されています。プログラムなどから利用する場合、これらの API を利用したエスケープ関数が提供されていると思います。

識別子

識別子はデータ型ではありませんが、識別子 (テーブル名、フィールド名など) も文字列型と同様に定義できます。英数字のみの識別子はクオート無しで利用できますが、それ以外の場合は ” (ダブルクオート) でクオートします。

  • “識別子”
  • U&”識別子”

の形式が利用できます。テーブル名、フィールド名などに空白文字など意味がある文字を利用する場合にはクオートが必要です。予約語もクオートすれば識別子として利用できます。識別子は name 型を利用しているので最大の長さは 63バイトに制限されます。

この例では a カラムを “data” として表示しています。

この例では予約語である SELECT をクオートして識別子として利用しています。

今の PostgreSQL は日本語名の識別子もクオート無しで利用できます。長すぎる識別子はまるめられ、その結果無効な文字エンコーディングになるとエラーになります。

識別子はクオートしないと小文字に変換されてしまいますが、クオートすれば大文字小文字を維持できます。

大文字小文字を区別した定義を行うとクエリの際にもクオートが必要になります。

■識別子のエスケープ

識別子も文字列型と同様にエスケープします。エスケープには ” (ダブルクオート) を利用します。

まとめ

PostgreSQL の文字列に関する情報を簡単にまとめてみました。PostgreSQL マニュアルにはより詳しい情報が記載されています。簡単にまとめましたが普段は利用しないような機能でこんな使い方もあるのか?など、参考になれば幸いです。

参考:
http://www.postgresql.jp/document/9.5/html/datatype-character.html
http://www.postgresql.jp/document/9.5/html/datatype-binary.html
http://www.postgresql.jp/document/9.5/html/functions-string.html
http://www.postgresql.jp/document/9.5/html/libpq-exec.html

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。

コメント投稿

メールアドレスは表示されません。


*