バイナリ文字列関数と演算子. 別名データ型は使用できません。Alias data types cannot be used. lengthlength データ型でユーザー指定の長さが許可されるとき、ターゲット データ型の長さを指定する任意の整数。An optional integer that specifies the length of the target data type, for data types that allow a user specified length… PostgreSQL 8.1.9 文書 PostgreSQL ... SQL文字列関数と演算子 9-6. 同様に、(to_char以外用)入力テンプレート文字列では、テンプレートパターンは入力されたデータ文字列の探し出される部分と、そこで見つけ出される値を特定します。, ある種の修飾子はどのようなテンプレートパターンに対しても、その振舞いを変更するために適用することができます。例えば、FMMonthはFM修飾子の付いたMonthパターンです。表9-22に、日付/時刻書式の修飾子パターンを示します。, FMはパターンの出力を固定長にするため、先頭にはゼロ、末尾には空白を追加してしまう機能を無効にします。, FXオプションがテンプレートが使用されていない場合には、to_timestampとto_dateは入力文字列の複数の空白スペースを無視します。FXはテンプレートの第1項目として指定される必要があります。例えば、to_timestampにはたった1つのスペースがあることになっているので、to_timestamp('2000    JUN', 'YYYY MON')が正しく、to_timestamp('2000    JUN','FXYYYY MON')はエラーを返します。, to_charテンプレートでは、通常のテキストが許され、そのまま出力されます。部分文字列を二重引用符で括ることで、部分文字列にパターン用のキーワードがあったとしても、強制的にリテラルテキストとして解釈させることができます。例えば、'"Hello Year "YYYY'ではYYYYは年データに置換されてしまいますが、Year内のYは置換されません。, 出力に二重引用符を付けたい場合、'\\"YYYY Month\\"'のようにその前にバックスラッシュを付けなければなりません(文字列定数内のバックスラッシュは既に特別な意味を持つため、2つのバックスラッシュが必要です)。, 文字列をtimestamp型もしくはdate型にするYYYY変換は、年の値として4桁以上の数字を使用していると制限が加えられます。このような場合、数字以外の文字またはYYYYの後にテンプレートを使わなければなりません。 そうしないと年は常に4桁と解釈されます。例えば(20000年として)、to_date('200001131', 'YYYYMMDD')は4桁の年と解釈されるので、to_date('20000-1131', 'YYYY-MMDD')またはto_date('20000Nov31', 'YYYYMonDD')のように数字でない区切り符号の使用をお勧めします。, 文字列からtimestampもしくはdateへの変換において、YYY、YYYY、もしくはY,YYYフィールドが存在するとCCフィールドは無視されます。CCがYYもしくはYと共に使用されると、年は(CC-1)*100+YYのように計算されます。, 文字列型からtimestamp型への変換に際し、ミリ秒MSおよびマイクロ秒USの値は小数点の位置の後の秒の部分として使用されます。例えば、to_timestamp('12:3', 'SS:MS')は3ミリ秒ではなく300ミリ秒です。なぜなら変換においてこれは12 + 0.3と計算されるからです。ということは、SS:MS書式に対して入力値である12:3、12:30、および12:300は同じミリ秒数を指定します。3ミリ秒数が必要な場合には12:003のようにしなければなりません。この時、変換において12 + 0.003 = 12.003秒と計算します。, もう少し複雑な例を挙げます。 構文 9.5. 例えば、to_char(-12, 'S9999')は'  -12'となる一方、to_char(-12, 'MI9999')は'-  12'となります。 文字列型からtimestamp型への変換に際し、ミリ秒MSおよびマイクロ秒USの値は小数点の位置の後の秒の部分として使用されます。 例えば、 to_timestamp('12:3', 'SS:MS') は3ミリ秒ではなく300ミリ秒です。 ここでnはVに続く桁数です。 <20190611 追記> 何故か最近この記事へのアクセスが急増しています PostgreSQLは結局ほとんど使っていないので、変なところとかもっと良い方法があるよとかそういうのはどしどし突っ込んでいただけるとありがたいです。 </追記>まだうまく飲み込めてないのでなんともですが、 PostgreSQL … HH12のようにフォーマットします。一方HH24は一日を越える時間を >24 のように出力します。, SG、PL、またはMIで整形された符号は、数値と関連付けられません。 xml、bigint、sql_variant が含まれます。This includes xml, bigint, and sql_variant. PostgreSQLでの日付データ文字列変換 まあInformixについて書いたのは書式指定方法だが、PostgreSQLにも同じような関数がある。 関数自体はto_charなので同じ。 CONVERT 関数で日付型のデータを文字列に変換する際にスタイル (書式) が設定できます。普段の開発業務では、111 (yyyy/mm/dd), 112 (yyyymmdd) をよく使いますが、どれくらいの種類があるのか気に … <20190611 追記> 何故か最近この記事へのアクセスが急増しています PostgreSQLは結局ほとんど使っていないので、変なところとかもっと良い方法があるよとかそういうのはどしどし突っ込んでいただけるとありがたいです。 </追記>まだうまく飲み込めてないのでなんともですが、 PostgreSQL … アプリケーションに高精度な値を保存するような金融ドメインの場合、数値/10 進数として設定するかもしれません。そうでなければ、倍精度または浮動小数点で十分でしょう。, 次のコードを使って、外部データベースからテーブルにアクセスするユーザーマッピングを作成します。, すべての外部テーブルをローカルスキーマにインポートし、通常のテーブルにアクセスするのと同じように外部テーブルからデータにアクセスします。以下は、外部データベースとスキーマからテーブルをインポートするサンプルコードです。. --> SQL Binary String Functions and Operators 9-9. Oracleの実装では9に先行してMIが置かれてはならず、9の後にMIが置かれることを要求しています。, 9は9が並んでいる数と同じ桁数の値を出力します。 PostgreSQLでの日付データ文字列変換 まあInformixについて書いたのは書式指定方法だが、PostgreSQLにも同じような関数がある。 関数自体はto_charなので同じ。 テンプレートパターンではない全てのテキストは単にそのままコピーされます。 PostgreSQL は、オープンソースのリレーショナルデータベースの中でも最も人気のあるシステムの 1 つです。Oracle や Microsoft SQL Server などの商用データベースから移行する場合、最高のデータベースの選択肢の 1 つと言ってもいいでしょう。AWS には 2 つのマネージド PostgreSQL オプションである Amazon RDS と Amazon Aurora があります。, マネージド PostgreSQL サービスに加えて、AWS は移行を支援するツールやリソースも提供しています。AWS Schema Conversion Tool (SCT) は既存のスキーマの変換や、複数のソースデータベースとターゲットデータベースをサポートする無料の AWS ツールです。AWS には AWS Database Migration Service (DMS) もあります。これは異種データベース間および同種データベース間でデータを転送し、継続的にレプリケートを行うのに役立ちます。同様に、商用データベースと PostgreSQL などのオープンソースデータベースとの間の多数の機能マッピングを文書化した移行プレイブックを提供しています。, この投稿では、コードを PL/SQL から PL/pgSQL に変換するためのヒントとベストプラクティスをご紹介します。これにより、パフォーマンスの向上や PostgreSQL へのコード変換を実行できます。この投稿は、データベースの移行に取り組む開発者を対象としており、読者はデータベースと PL/SQL の基本的な知識があることを前提としています。, このセクションでは、SQL Server や Oracle などの商用データベースまたはレガシーデータベースから移行する際に、PostgreSQL のパフォーマンスの向上に影響するいくつかの要因について説明しています。データベースにはたいてい類似したオブジェクトがありますが、正しいオブジェクトについて考慮すると、システムの動作が改善されます。このセクションでは、ストアドプロシージャ、関数、SQL ステートメントを使ってパフォーマンスを向上させる方法について説明します。, 作業のやり直しを避けるには、プロジェクトを開始する前に、ターゲットデータベースのデータ型をソースシステムに正しくマッピングします。次のテーブルは、Oracle および SQL Server から PostgreSQL への一般的なデータ型マッピングをまとめたものです。, データベースから最高のパフォーマンスを得るには、最適なデータ型を使用することが重要です。, テーブル列に最大 4 桁の数字を含める必要がある場合、4 (整数/実数)、8 (bigint/倍精度)、または変数 (数値) のバイトデータ型を定義するのではなく、2 (smallint) バイトの列データ型で十分です。, 数値は 131,000 桁を保持できる複合型のため、正確さが必要な金額やその他の数量の保存に推奨されます。しかし演算子が遅いため、数値の計算は整数型または浮動小数点型に比べて極めて遅くなります。, 次のテーブルは、インデックスを除く非精度列の数値サイズを smallint/int/bigint と比較したときに、単一列のテーブルサイズがどのように増加するかを示しています。, 次のテーブルでは前のテーブルと同じ情報を使用していますが、インデックスが含まれています。このテーブルでは、サイズはテーブルの合計サイズを指し、外部サイズはインデックスなどの関連オブジェクトのサイズを指します。, AWS SCT は実際のデータサイズが分からなくても、テーブルの数値データ型に数値をマッピングします。このツールには、変換中に正しいデータ型を設定/マッピングするオプションがあります。, PostgreSQL 10 以前のバージョンには、プロシージャのサポートがありません。Oracle および SQL Server のすべてのプロシージャと関数は、PostgreSQL の関数にマッピングされます。これらのプロシージャはバージョン 11 以降の PostgreSQL でサポートされており、Oracle に類似しています。, PostgreSQL は、Volatile、Stable、Immutable の 3 つの変動性カテゴリの関数をサポートしています。移行中は、機能に基づいて適切な型を使用する必要があります。パフォーマンスの調整には、関数型を適切にマークすることが重要です。, volatile 型は 1 回のテーブルスキャン内でも関数値が変更する可能性があります。そのため、最適化ができません。比較的少ないですが、データベース関数は発揮性です。Random()、currval()、timeofday() がそれらの例です。副作用のある関数は、結果が予測可能であっても、呼び出しが最適化されないように発揮性として分類する必要があります。その例の 1 つが setval() です。関数の作成中に volatility 型がない場合、すべての新しい関数はデフォルトで volatile 型としてマークされます。, Stable 型は、関数がデータベースを変更できないことを示します。単一のテーブルスキャン内で、同じ引数値に対して一貫して同じ結果を返しますが、その結果は SQL ステートメント間で変わる可能性があることも示しています。これは、結果が現在のタイムゾーンなどのデータベースルックアップまたはパラメーター変数に依存する関数に適しています。関数の current_timestamp ファミリーが値がトランザクション内で変更されないため、安定と見なされます。, Immutable 型は関数がデータベースを変更できず、同じ引数値が指定されたときには常に同じ結果を返すことを示します。つまり、データベースのルックアップを行わない、または引数リストに直接存在しない情報を使用しないことを意味します。このオプションを指定すると、すべて定数の引数を使用した関数の呼び出しを関数の値に即座に置き換えることができます。, 上記のすべての関数は同じ値を返しますが、パフォーマンスを向上させるには、機能に応じてこれら 3 つの関数タイプのいずれかを使用する必要があります。, これらの各関数のテストの実行は、関数に同じ機能が含まれていることを示していますが、Immutable バリアントでは最小限の時間しかかかりません。これは、このカテゴリによって、オプティマイザが定数引数を使用したクエリ呼び出し中に関数を事前評価できるためです。, 多くのアプリケーションでは、関数呼び出しを含むビューとクエリを使用しています。前のセクションで説明したように、PostgreSQL では特に関数の揮発性のカテゴリが正しく設定されていない場合、コストが高くつく操作となる可能性があります。これに加えて、関数呼び出し自体がクエリコストに追加されます。, 関数の機能に基づいて、関数に適切な揮発性を選択します。関数が本当に Immutable または Stable である場合、デフォルトの Volatile を使用せずにこれらを設定すると、パフォーマンスが向上します。, 関数 getDeptname() は volatile としてマークされています。クエリの合計ランタイムは 2 秒と 886 ミリ秒です。, getDeptname() 関数安定していると言えます。クエリの合計ランタイムは 2 秒と 644 ミリ秒です。, 関数のロジックがクエリに正常に移動しました。クエリの合計ランタイムは 933 ミリ秒です。, PostgreSQL には、Exception および Raise ステートメントを使用して、エラーをトラップおよび発生させる機能があります。これは便利な機能ですが、コストがかかります。, Raise ステートメントは、PL/pgSQL 関数の操作中にエラーと例外を発生させます。デフォルトでは、PL/pgSQL 関数内でエラーが発生すると、関数は実行を中止し、変更をロールバックします。エラーから回復するには、PL/pgSQL は Exception 句を使用してエラーをトラップします。この機能を使用するには、PostgreSQL は例外処理を伴うコードブロックに入る前にトランザクションの状態を保存する必要があります。これは高くつく操作のため、オーバーヘッドコストが追加されます。, このオーバーヘッドを回避するには、アプリケーション側で例外をキャッチするか、関数が例外を発生させないように必要な検証を確実に行うことを確認することをお勧めします。, 次のコード例は、関数呼び出しに例外が発生するためにパフォーマンスに影響があることを示しています。, 例外が発生せず検証できないのであれば、明らかに例外が必要です。前の例では、診断をチェックして、処理された変更があるかどうかを確認できます。可能であれば、例外処理の使用を避けることをお勧めします。, たいていのアプリケーションは合計カウントを取得し、カーソルをループしてレコードを取リ込みます。レコードが存在しない場合、フェッチ操作は null を返すため、別の 2 つの変数を宣言してカウントをチェックすることでカウントをループするのではなく、フェッチステータスを使用することをお勧めします。実行するステートメントの数を減らしてパフォーマンスを向上させることにより、余分な変数の宣言や増加値のチェックを回避できます。例として次のコードをご参照ください。, 以下のように、このコードを書き換えることもできます。これで、2 つの変数の宣言を回避でき、カーソル自体を使用して反復し、カーソルステータスを使用してループを中断/終了できます。, レガシーアプリケーションでは、SQL クエリを記述して一致するレコードの数を見つけて、必要なビジネスロジックを適用します。テーブルに数十億のレコードがある場合、レコードカウントの取得にコストがかかる可能性があります。, このコードは、行全体ではなく 1 つの列をチェックするように書き換えることもできます。こうすると、コストとパフォーマンスの効率を向上できます。以下のサンプルコードをご参照ください。, ほとんどのレガシーアプリケーションでは、レコード数はデータ操作ステートメントに変更があるかどうかを示します。PostgreSQL では、この情報は統計情報として保持されます。これを取得して、操作後の値のカウントを回避することができます。以下のコードサンプルが示すように、診断を使って、影響を受ける行の数を取得します。, ワイルドカード文字 % または _ を LIKE (あるいは区別しない検索の場合は ILIKE) 式とともに使用して、テーブルからデータを取得するのが一般的です。ワイルドカード文字が指定されたパターンの先頭にある場合、クエリプランナーはインデックスが存在してもインデックスを使用できません。このような場合、シーケンシャルスキャンを使用しますが、時間がかかります。クエリプランナーが利用可能なインデックスを使用できるようにして、数百万のレコードでパフォーマンスを向上するには、述語の先頭ではなく中央または末尾にワイルドカード文字を使用します。こうすると、プランナーがインデックスを使用するように強制します。, LIKEし 式に加えて、pg_trgm モジュール/拡張機能をパターンマッチングに使用することもできます。pg_trgm モジュールは、英数字テキストの類似性を判断する関数と演算子を提供します。同様の文字列の高速検索をサポートするインデックス演算子クラスも提供します。詳細については、PostgreSQL ウェブサイトの pg_trgm ドキュメントをご参照ください。, このセクションでは、Oracle、SQL Server、PostgreSQL データベース全体で SQL ステートメントを作成する際のデータベース固有の比較について説明します。, Oracle では、FROM 句は必須です。これは、コード Select 1 from Dual; を使用します。PostgreSQL および SQL の場合、コード Select 1; の使用はオプションです。, Oracle の場合、開始番号は必要ありませんが、終了番号を指定できます。例として次のコードをご参照ください。, 詳細については、Oracle データベースのサイトにある「SQL for Beginners (Part 5): Joins」をご参照ください。, PostgreSQL または SQL Server には、テーブルに左結合または右結合を行う「+」の機能はありません。代わりに、次の 2 つのクエリを使用します。, SQL Server では、Type データ型の複数のレコードを渡すことができます。PostgreSQL で同じものを実装するには、JSON 形式または配列の JSON データ型あるいはテキストデータ型としてこれを使用します次のコード例は、複数のレコードを含む JSON 形式のテキストデータ型を使用しています。これを一時テーブルに挿入し、次のコードを使ってさらに処理を実行できます。, 次のコードは、Oracle の varchar データ型で複数のレコードを渡す方法を示しています。, 次のコードは、上記の Oracle での機能と同じく、SQL Server のテーブル型で複数のレコードを渡す方法を示しています。, 次のコードは、Oracle および SQL Server で上記に示したものと同じ機能において、PostgreSQL で複数のレコードをテキスト型として渡す方法を示しています。, PostgreSQL ではピボット機能は有効になっていないため、拡張が必要です。拡張機能 tablefuncは、SQL Server や Oracle と同様にピボットテーブルの作成に使用する crosstab 関数を有効にします。以下は、Oracle、SQL Server、PostgreSQL のピボット機能のコードです。, PostgreSQLには Unpivot 関数がありません。SQL Server または Oracle から PostgreSQL に変換する場合、ピボット解除は配列にマッピングされます。例については、次のコードをご参照ください。, 次のサンプルコードを使用して、SQL Server のピボット解除機能を実装します。, 次のサンプルコードを使用して、PostgreSQL のピボット解除機能を実装します。, SQL Server が複数の行を持つ複数の結果セットは、簡単に返すことができます。以下のサンプルのようにカーソルを使用すれば、PostgreSQL と Oracle で同じように実行できます。, 次のコードを使用して、Oracle のプロシージャから複数の結果セットを返します。, 次のコードを使用して、SQL Server のプロシージャから複数の結果セットを返します。SQL Server では追加のパラメーターは必要ありません。.