この章ではSQLの構文について説明します。 この章の内容は、データの定義や変更のために SQL コマンドを適用する方法について詳しく説明する以後の章を理解する上での基礎となります。
ルールや概念によっては SQL データベースによって異なっていたり、あるいは PostgreSQL に固有のものなどがありますので、SQL について熟知しているユーザもこの章を注意深く読むことをお勧めします。
SQL の入力は、ひと続きのコマンドから成ります。コマンドはトークンが繋がったもので構成され、最後はセミコロン(";")で終わります。入力ストリームの終了もやはりコマンドを終わらせます。どのトークンが有効かは特定のコマンドの構文によります。
トークンはキーワード、識別子、引用符で囲まれた識別子、リテラル(もしくは定数)、特別な文字シンボルです。トークンは通常空白(スペース、タブ、改行)で区切られますが、あいまいさがなければ(一般的には特別な文字が他のトークン型と隣接している場合のみ)必要ありません。
さらに、入力された SQL にコメントが付いていても構いません。コメントはトークンではありませんが、効果は空白と同じです。
たとえば、以下のものは(構文的に)正しい SQL の入力です。
SELECT * FROM MY_TABLE; UPDATE MY_TABLE SET A = 5; INSERT INTO MY_TABLE VALUES (3, 'hi there');
この例は 1 行に 1 つのコマンドを記述した、3 つのコマンドが連続しています(必ずしも1つのコマンドを1行で書く必要はありません。 1行に複数のコマンドを入力することも可能ですし、1つのコマンドを複数行に分けて記述することも可能です)。
SQL 構文は、どのトークンがコマンドを識別し、どれがオペランドでどれがパラメータかに関してはさほど首尾一貫していません。最初のいくつかのトークンは一般にコマンド名です。 したがって、上記の例において"SELECT"、"UPDATE"、"INSERT" コマンドについて通常説明することになります。しかしたとえばUPDATE コマンドでは、SET トークンが特定の位置に常に記述されなければなりませんし、この例で使われている INSERT コマンドを完結するためにはVALUES トークンが必要です。 それぞれのコマンドの正確な構文のルールは PostgreSQL リファレンスマニュアル で説明されています。
上記の例に出てくるSELECT、UPDATE、もしくは VALUES のようなトークンはキーワードの一例です。 キーワードとは、SQL 言語で決まった意味を持っている単語です。MY_TABLE トークンや A トークンは識別子の一例です。これらは、使われるコマンドによって、テーブル、列、他のデータベースオブジェクトの名前を識別します。 したがって、"名前"とだけ呼ばれることもあります。キーワードと識別子は同じ語彙の構造を持つため、言語を知らなくてはトークンが識別子なのかキーワードなのかわからないということになります。全てのキーワードのリストはAppendix Bにあります。
SQL 識別子とキーワードは、アルファベット(a〜z)か読み分け記号と非 Latin 文字、アンダースコア(_)で始まらなければいけません。識別子またはキーワードの中で続く文字は、アルファベット、数字(0〜9)、アンダースコアが使えますが、SQL 標準では数字が入っている、あるいはアンダースコアで始まったり終わったりするキーワードは定義されていません。
システムは NAMEDATALEN-1 以上の識別子の文字数を使いません。 より長い名前をコマンドで書くことはできますが、短く切られてしまいます。 デフォルトでは NAMEDATALEN は 64 なので、識別子は最長で 63 です (もっとも、PostgreSQL を構築する際に NAMEDATALEN は src/include/postgres_ext.h の中で変更できます)。
識別子とキーワード名は大文字と小文字を区別しません。 従って、
UPDATE MY_TABLE SET A = 5;
は、以下の文と同じ意味になります。
uPDaTE my_TabLE SeT a = 5;
慣習的には、キーワードを大文字で、名前を小文字で書きます。 たとえば下記のようになります。
UPDATE my_table SET a = 5;
識別子には他の種類もあります。区切り識別子あるいは引用符付き識別子です。文字の特定のに連なりが二重引用符(")で囲まれてたものです。 区切り識別子はどのような場合でも識別子であって、キーワードではありません。ですから "select" は "select" という名前の列あるいはテーブルを問い合わせるために使えますが、引用符のつかないselect はキーワードとして理解され、したがってテーブルもしくは列名が期待される部分ではパースエラーを起こします。引用符付き識別子は下記の例のように書くことができます。
UPDATE "my_table" SET "a" = 5;
引用符付き識別子は、二重引用符自身以外であればどような文字でも使えます。 二重引用符を含めたい場合は、二重引用符を 2 つ入力します。この決まりがあることによって、普段使えないスペースやアンパサンド(&)があるようなテーブル名や列名を作ることが可能です。この場合においても長さの制限は適用されます。
引用符がつかない名前は常に小文字に解釈されますが、識別子を引用符で囲むことによって大文字と小文字が区別されるようになります。たとえば、識別子 FOO、foo、"foo" は PostgreSQL によれば同じものとして解釈されますが、"Foo" と"FOO" は、これら 3 つとも、またお互いとも違ったものとして解釈されます。 [1]
PostgreSQL には、3 つの暗黙に型付けされる定数があります。 文字列、ビット文字列、そして数字です。定数は明示的な型で指定することもでき、その場合はシステムによる、より正確な表現と効率のよい操作が可能になります。暗黙の定数については以下に説明します。 明示的な定数は後ほど説明します。
SQL の文字列定数は任意の文字の並びを単一引用符("'")で囲ったもので、'This is a string' のようになります。SQL では2 つ連続した単一引用符を入力することにより、単一引用符を文字列の中に埋め込むことができます(例:'Dianne''s horse')。PostgreSQL では、単一引用符の代わりにバックスラッシュ("\")を使うこともできます。 (例:'Dianne\'s horse')
C 言語形式のバックスラッシュによるエスケープも有効です。\b はバックスペース、\fは改頁、\n は改行、\r は復帰(キャリッジリターン)、\t はタブ、xxx が八進数である場合、\xxx は対応するASCII コードを意味します。ここに示した以外のバックスラッシュに続く文字は、文字通りに解釈されます。したがって、文字列定数にバックスラッシュを含む場合は、バックスラッシュを 2 つ入力してください。
文字コード 0 の文字は文字列定数の中には含められません。
2 つの文字列定数が、少なくとも 1 つの改行を含んだ空白で区切られている場合は、2 つの定数は連結されて、あたかも 1 つの定数として書かれたように効率的に処理されます。例を示します。
SELECT 'foo' 'bar';
は次と同等です。
SELECT 'foobar';
しかし、
SELECT 'foo' 'bar';
は有効な構文ではありません。 (この奇妙な振る舞いは SQL で決められているもので、PostgreSQL ではこの標準に従っています)
ビット文字列定数は B(大文字もしくは小文字)が引用符の始まりの前についている(間に空白はありません)文字列定数のように見えます。 たとえば B'1001' のようになります。ビット文字列定数の中で許可される文字は0 と 1 のみです。
その他にも、ビット文字列定数は X'1FF' といった具合に、先頭に X (大文字または小文字) を使用して 16 進表記で指定することもできます。 この表記は、各 16 進数値をそれぞれ 4 つの 2 進数値に置き換えたビット文字列定数と同等です。
どちらの形式のビット文字列定数でも、通常の定数と同じように複数行にわたって続けて書くことができます。
数値定数は下記の一般的な形で受け付けられます。
digits digits.[digits][e[+-]digits] [digits].digits[e[+-]digits] digitse[+-]digits
ここで digits は 1 つ以上の 10 進数字 (0 〜 9) です。 小数点を使用する場合は、少なくとも 1 つの数字が小数点の前か後になくてはなりません。 e の付く形式を使う場合には e の後に少なくとも 1 つの数字がなければいけません。 スペースや他の文字は、定数の中に埋め込むことはできません。 プラスまたはマイナスの符号を先頭につけても、定数の一部とは見なされないことに注意してください。 これらの符号は定数に適用される演算子と見なされます。
下記は有効な数値定数のいくつかの例です。
42
3.5
4.
.001
5e2
1.925e-3
小数点も指数句も含まない数値定数の場合、まずその値が integer 型 (32 ビット) に適合すれば integer 型であるとみなされます。そうでない場合、bigint 型 (64ビット) で適合すれば bigint 型とみなされます。どちらでもない場合は、numeric 型とみなされます。 定数が小数点または指数句あるいはその両方を含む場合は、常に最初に numeric 型であるとみなされます。
数値定数に最初に割り振られるデータ型は、型解決アルゴリズムの開始点にすぎません。 ほとんどの場合、定数にはコンテキストに基づいて自動的に正しい型が割り振られます。 必要であれば、特定のデータ型をキャストして、数値がそのデータ型として解釈されるように強制することができます。 たとえば、以下のようにして数値を real 型 (float4) として処理することができます。
REAL '1.23' -- 文字列表記 1.23::REAL -- PostgreSQL (歴史的な) 形式
任意の型の定数は下記の表記のいずれかを使って入力することができますす。
type 'string' 'string'::type CAST ( 'string' AS type )
文字列のテキストは type と呼ばれる型の入力変換ルーチンへと渡されます。結果は指示された型の定数です。明示的な型キャストは、もし定数がどの型でなければならないかについて曖昧な点がなければ(たとえば引数がオーバーロードされていない関数に渡される場合)省略しても構いません。 その場合自動的に型強制されます。
関数のような構文を使って型強制を指定することも可能です。
typename ( 'string' )
しかし、全ての型の名前でこの方法は使用できません。 詳細はSection 1.2.6 を参照して下さい。
::、CAST() や関数呼び出し構文も、Section 1.2.6 で説明した通り、任意の式の実行時の型変換を指定するために使うことができます。しかし、type'string'という形式はリテラル定数を指定する場合にのみ使うことができます。この他のtype 'string'は、配列型では動作しないことです。 配列型の定数を指定する場合は::か CAST() を使用して下さい。
一般的な配列定数のフォーマットは以下のとおりです。
'{ val1 delim val2 delim ... }' }'
delim は型の区切り文字で、pg_type の項目に書かれているものです(すべての組み込み型にとって、これはコンマ「,」です)。それぞれの val は配列要素の型の定数か部分配列です。配列定数の例は以下のようになります。
'{{1,2,3},{4,5,6},{7,8,9}}'
この定数は二次元で、整数の 3 つの部分配列によって成り立つ 3×3 の配列です。
個々の配列要素は、空白の曖昧さの問題を回避するため、二重引用符(")の間に置くことができます。引用符なしでは、配列値のパーサーは最初の空白をとばしてしまいます。
(配列定数は実際は、前節で説明された一般的な型の定数の特別な事例でしかありません。配列定数は始めは文字列として扱われ、配列入力変換ルーチンに渡されます。明示的な型の指定が必要な場合があるかもしれません。)
演算子はNAMEDATALEN-1までの(デフォルトは63です)長さの、以下に示すリストに含まれる文字の並びです。
+ - * / < > = ~ ! @ # % ^ & | ` ? $
しかし、演算子の名前にはいくつかの制約があります。$(ドル記号)は、複数文字の演算子名になることはできますが、単一文字の演算子にはなることができません。
-- と「/*」は演算子名の中に使うことができません。 なぜならこれらはコメントの始まりと解釈されるからです。
複数文字の演算子名は、その名前が少なくとも下記の文字の 1 つ以上を含まない限り、「+」や「-」で終わることができません。
~ ! @ # % ^ & | ` ? $
たとえば、@- は演算子名として認められていますが、「*-」は認められていません。この制限によりPostgreSQLは、SQLに準拠する問い合わせをトークン同士の間にスペースを要求せず、解析することができます。
非SQL標準の演算子名を使う場合、通常は曖昧さを回避するために、隣り合った演算子をスペースで区切る必要があります。 たとえば @ という左単項演算子を定義した場合、X*@Y とは書けません。PostgreSQL がこれを確実に 1 つではなく 2 つの演算子名として解釈できるように、X* @Y と書く必要があります。
英数字ではないいくつかの文字は、演算子であることとは異なる特殊な意味を持っています。使用法の詳細はそれぞれの構文要素が説明されている場所で見つけることができます。この節の目的は単に存在を知らせ、これらの文字の目的をまとめることに留めます。
直後に数字が続くドル記号(「$」)は、関数定義の本体中の位置パラメータを表すために使われます。他の文脈ではドル記号は演算子名の一部であるかもしれません。
かっこ(「()」)は、通常どおり式をまとめ優先するという意味を持ちます。場合によってはかっこは、特定の SQL コマンドの固定構文の一部として要求されることがあります。
大かっこ(「[]」)は、配列要素を選択するために使われます。配列に関する詳しい情報はSection 5.12を参照してください。
カンマ(「,」)は、リストの要素を区切るために構文的構造体で使われることがあります。
セミコロン(「;」)は、SQLコマンドの終わりを意味します。文字列定数または引用符付き識別子以外では、コマンドの途中では使うことができません。
コロン(「:」)は、配列から"一部分"を取りだすために使われます(Section 5.12を参照)。いくつかの SQL 方言(埋め込み SQL など)では、コロンは変数名の接頭辞として使われます。
アスタリスク(「*」)は、SELECT コマンドの中、あるいはCOUNT 集約関数と一緒に使われる場合、特殊な意味を持ちます。
ピリオド (「.」) は、浮動小数点定数の中で使われます。また、スキーマ、テーブル、列名を区切るためにも使われます。
コメントは二重ハイフンで始まる任意の文字の並びで、行の終わりまで続きます。 たとえば以下のようになります。
-- これは標準 SQL92 のコメントです
代わりに、C 言語形式のブロックコメントも使えます。
/* ネストされた * 複数行コメント/* ネストされたブロックコメント*/ */
コメントは /* で始まり、対応する /* で終わります。 これらのブロックコメントは C とは異なり、SQL99 で指定されているようにネストできます。したがって、既存のブロックコメントを含む可能性のある大きなコードのブロックをコメントアウトすることができます。
コメントは、さらに構文解析が行われる前に入力ストリームから取り去られ、空白によって適切に置き換えられます。
Table 1-1 は、PostgreSQL の演算子の優先順位と結合性を示しています。ほとんどの演算子は同じ優先順位を持ち左結合します。演算子の優先順位と結合性はパーサーに組み込まれています。これは非直感的な動作に導く可能性があります。 たとえばブーリアン演算子<と> は、ブーリアン演算子<=と>= とは違った優先順位を持ちます。さらに、二項と単項演算子を組み合わせて使う場合はかっこを加える必要がある場合があります。たとえば下記のような場合です。
SELECT 5 ! - 6;
これは下記のように解析されます。
SELECT 5 ! (- 6);
なぜならば、パーサーは「!」が中置ではなく接尾演算子として定義されていることに最後まで気がつかないためです。この場合、求める結果を得るためには下記のように書く必要があります。
SELECT (5 !) - 6;
これが拡張性を求めるゆえの代償です。
Table 1-1. 演算子の優先順位(強いものから)
演算子/要素 | 結合性 | 説明 |
---|---|---|
. | 左 | テーブル/列名の区切り |
:: | 左 | PostgreSQL方式の型キャスト |
[ ] | 左 | 配列要素選択 |
- | 右 | 単項減算 |
^ | 左 | 羃乗 |
* / % | 左 | 掛け算、割り算、剰余 |
+ - | 左 | 加算、減算 |
IS | IS TRUE、IS FALSE、IS UNKNOWN、IS NULL | |
ISNULL | NULL かどうかを試す | |
NOTNULL | 非 NULL かどうかを試す | |
(any other) | 左 | その他すべての組み込み、あるいはユーザ定義の演算子 |
IN | メンバーシップを設定する | |
BETWEEN | 囲み | |
OVERLAPS | 時間間隔の重複 | |
LIKE ILIKE SIMILAR | 文字列パターンのマッチング | |
< > | 小なり、大なり | |
= | 右 | 等しい、代入 |
NOT | 右 | 論理否定 |
AND | 左 | 論理積 |
OR | 左 | 論理和 |
演算子優先順位のルールは、上記で触れた組み込み演算子と同じ名前を持つユーザ定義演算子にも当てはまります。たとえばもし"+" 演算子をあるカスタムデータ型に定義すると、新しい演算子が何をするかにかかわらず、組み込まれた "+" 演算子と同じ優先順位を持つようになります。
次の例のように、OPERATOR 構文でスキーマで修飾された演算子名を使用する場合は、
SELECT 3 OPERATOR(pg_catalog.+) 4;
OPERATOR 構文は、Table 1-1 の "any other" 演算子で示されているデフォルトの優先順位を持つとみなされます。 これは、OPERATOR() にどの特定の演算子が入る場合でも変わりません。
[1] | PostgreSQL が引用符の付かない名前を小文字として解釈することは SQL 標準とは相容れないもので、SQL 標準では引用符の付かない名前は大文字に解釈されるべきだとされています。したがってSQL 標準によれば、foo は "FOO" と同じであるべきで、"foo" ではないはずなのです。もし移植可能なアプリケーションを書きたいならば特定の名前は常に引用符で囲むか、あるいは全く囲まないのいずれかに統一することをお勧めします。 |