PolarDB:COPY

説明

COPYは、PolarDBテーブルと標準ファイルシステムファイル間でデータを移動します。 COPY TOはテーブルの内容をファイルにコピーしますが、COPY FROMはファイルのからテーブルにデータをコピーします (テーブルに既にあるものにデータを追加します) 。 COPY TOは、SELECTクエリの結果をコピーすることもできます。

列リストが指定されている場合、COPY TOは指定された列のデータのみをファイルにコピーします。 COPY FROMの場合、ファイル内の各フィールドが指定された列に順番に挿入されます。 COPY FROM列リストで指定されていないテーブル列には、デフォルト値が表示されます。

ファイル名を持つCOPYは、PolarDBサーバーにファイルの直接読み取りまたは書き込みを指示します。 ファイルにはPolarDBユーザー (サーバーが実行されるユーザーID) がアクセスでき、名前はサーバーの観点から指定する必要があります。 PROGRAMが指定されている場合、サーバーは指定されたコマンドを実行し、プログラムの標準出力から読み取り、またはプログラムの標準入力に書き込みます。 コマンドはサーバの観点から指定し、PolarDBユーザーが実行できるようにする必要があります。 STDINまたはSTDOUTが指定されている場合、クライアントとサーバー間の接続を介してデータが送信されます。

概要

COPY table_name [ ( column_name [, ...] ) ]
        FROM { 'filename' | プログラム 'command' | STDIN}
        [ [ WITH ] (オプション [, ...] ) ]
        [ WHERE condition ]

    COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
        TO { 'filename' | プログラム 'command' | STDOUT}
        [ [ WITH ] (オプション [, ...] ) ]

ここで、オプションは次のいずれかになります。

        FORMAT format_name
        FREEZE [ブール値]
        DELIMITER 'delimiter_character'
        NULL 'null_string'
        HEADER [ブール値]
        QUOTE 'quote_character'
        エスケープ 'エスケープ_文字'
        FORCE_QUOTE { ( column_name [, ...] ) | *}
        FORCE_NOT_NULL ( column_name [, ...] )
        FORCE_NULL ( column_name [, ...] )
        エンコード 'encoding_name' 

パラメーター

table_name: 既存のテーブルの名前 (スキーマ修飾) 。

colum_name: コピーする列のオプションのリスト。 列リストが指定されていない場合、生成された列を除くテーブルのすべての列がコピーされます。

query: 結果をコピーするSELECT、VALUES、INSERT、UPDATE、またはDELETEコマンド。 クエリの周囲には括弧が必要です。

INSERTの場合、UPDATEおよびDELETEクエリでは、RETURNING句を指定する必要があります。また、ターゲットリレーションには、複数のステートメントに拡張される条件付きルール、ALOTルール、INSTEADルールはありません。

filename: 入力ファイルまたは出力ファイルのパス名。 入力ファイル名には絶対パスまたは相対パスを指定できますが、出力ファイル名には絶対パスを指定する必要があります。 Windowsユーザーは、E ''文字列を使用し、パス名で使用されるバックスラッシュを2倍にする必要があります。

PROGRAM: 実行するコマンド。 COPY FROMでは、入力はコマンドの標準出力から読み取られ、COPY TOでは、出力はコマンドの標準入力に書き込まれます。

STDIN: クライアントアプリケーションからの入力を指定します。

STDOUT: 出力がクライアントアプリケーションに送信されることを指定します。

boolean: 選択したオプションをオンにするかオフにするかを指定します。 オプションを有効にするにはTRUE、ON、または1と記述し、オプションを無効にするにはFALSE、OFF、または0と記述します。 ブール値は省略することもでき、その場合はTRUEと仮定する。

FORMAT: 読み取りまたは書き込みするデータ形式を選択します: text、csv (コンマ区切り値) 、またはbinary。 デフォルトはtextです。

FREEZE: VACUUM FREEZEコマンドを実行した後と同じように、凍結済みの行を持つデータのコピーを要求します。 これは、初期データロードのパフォーマンスオプションとして意図されています。 行は、ロードされているテーブルが現在のサブトランザクションで作成または切り捨てられており、カーソルが開いておらず、このトランザクションで保持されている古いスナップショットがない場合にのみ凍結されます。 現在、パーティションテーブルに対してCOPY FREEZEを実行することはできません。

DELIMITER: ファイルの各行 (行) 内の列を区切る文字を指定します。 デフォルトは、テキスト形式のタブ文字、CSV形式のカンマです。 これは単一の1バイト文字でなければなりません。 バイナリ形式を使用する場合、このオプションは使用できません。

NULL: null値を表す文字列を指定します。 デフォルトはテキスト形式の \N (バックスラッシュ-N) で、CSV形式の引用符なしの空の文字列です。 空白文字列と空の文字列を区別したくない場合は、テキスト形式でも空の文字列を使用することをお勧めします。 バイナリ形式を使用する場合、このオプションは使用できません。

HEADER: ファイルに、ファイル内の各列の名前を含むヘッダー行が含まれることを指定します。 出力では、最初の行にテーブルの列名が含まれ、入力では、最初の行は無視されます。 このオプションは、CSV形式の場合にのみ使用できます。

QUOTE: データ値を引用するときに使用する引用文字を指定します。 デフォルトは二重引用です。 これは単一の1バイト文字でなければなりません。 このオプションは、CSV形式の場合にのみ使用できます。

ESCAPE: QUOTE値と一致するデータ文字の前に表示する文字を指定します。 デフォルトはQUOTE値と同じです (データに表示される場合、引用文字は2倍になります) 。 これは単一の1バイト文字でなければなりません。 このオプションは、CSV形式の場合にのみ使用できます。

FORCE_QUOTE: 指定した各列のNULL以外のすべての値にクォートを使用します。 NULL出力は引用されません。 * を指定すると、すべての列でNULL以外の値が引用されます。 このオプションは、COPY TOでのみ使用でき、CSV形式を使用している場合にのみ使用できます。

FORCE_NOT_NULL: 指定した列の値をnull文字列と一致させません。 null文字列が空のデフォルトの場合、これは、空の値が引用されていない場合でも、nullではなくゼロ長の文字列として読み取られることを意味します。 このオプションは、COPY FROMでのみ使用でき、CSV形式でのみ使用できます。

FORCE_NULL: 指定された列の値を、引用されている場合でも、null文字列と照合し、一致が見つかった場合は値をNULLに設定します。 null文字列が空のデフォルトの場合、これは引用符で囲まれた空の文字列をNULLに変換します。 このオプションは、COPY FROMでのみ使用でき、CSV形式でのみ使用できます。

ENCODING: ファイルがencoding_nameでエンコードされることを指定します。 このオプションを省略した場合は、現在のクライアントエンコーディングが使用されます。 詳細については、以下のノートを参照してください。

WHERE: オプションのWHERE句の一般的な形式

WHERE条件

条件は、ブール型の結果に評価される式です。 この条件を満たさない行は、テーブルに挿入されません。 実際の行の値が変数参照に置き換えられたときにtrueを返す場合、行は条件を満たします。

現在、WHERE式ではサブクエリは許可されておらず、評価にはCOPY自体による変更は表示されません (これは、式にVOLATILE関数の呼び出しが含まれている場合に重要です) 。

出力

正常に完了すると、COPYコマンドは形式のコマンドタグを返します。

コピー数

カウントは、コピーされた行の数です。

注

COPY TOは、ビューではなくプレーンテーブルでのみ使用できますが、COPY (SELECT * FROM viewname) TO ... ビューの現在のコンテンツをコピーできます。

COPY FROMは、プレーンテーブル、外部テーブル、パーティションテーブル、またはINSTEAD OF INSERTトリガーを持つビューで使用できます。

COPYは上記のテーブルのみを処理し、子テーブルとの間でデータをコピーしません。 たとえば、COPYTOはSELECT * FROM ONLYと同じ行をコピーします。 構文コピー (SELECT * FROM) TO...を使用して、継承階層、パーティションテーブル、またはビューのすべての行をダンプできます。

値がCOPY TOによって読み取られるテーブルに対してselect特権を持ち、値がCOPY FROMによって挿入されるテーブルに対してinsert特権を持つ必要があります。コマンドにリストされている列に対する列特権があれば十分です。

テーブルに対して行レベルのセキュリティが有効になっている場合、関連するSELECTポリシーがCOPYTOステートメントに適用されます。 現在、行レベルのセキュリティを持つテーブルでは、COPY FROMはサポートされていません。 代わりに、同等のINSERTステートメントを使用します。

COPYコマンドで指定されたファイルは、クライアントアプリケーションではなく、サーバーによって直接読み書きされます。 したがって、クライアントではなくデータベースサーバーマシンに存在するか、アクセス可能である必要があります。 クライアントではなく、PostgreSQLuser (サーバーが実行するユーザーID) によってアクセス可能で、読み取り可能または書き込み可能である必要があります。 同様に、PROGRAMで指定されたコマンドは、クライアントアプリケーションではなくサーバーによって直接実行され、PostgreSQLuserによって実行可能である必要があります。 COPYファイルまたはコマンドの名前付けは、pg_read_server_files、pg_write_server_files、またはpg_execute_server_programのいずれかのロールを付与されたデータベースのスーパーユーザーまたはユーザーにのみ許可されます。

COPYをthepsqlinstructionと混同しないでください。 \copyは、COPY FROM STDINまたはCOPY TO STDOUTを呼び出し、psqlclientからアクセス可能なファイルにデータをフェッチ /格納します。 したがって、\copyが使用される場合、ファイルのアクセシビリティとアクセス権はサーバーではなくクライアントに依存します。

COPYで使用するファイル名は必ず絶対パスで指定することをお勧めします。 これは、COPY TOの場合はサーバーによって適用されますが、COPY FROMの場合は、相対パスで指定されたファイルから読み取るオプションがあります。 パスは、クライアントの作業ディレクトリではなく、サーバープロセスの作業ディレクトリ (通常はクラスターのデータディレクトリ) に対して相対的に解釈されます。

PROGRAMを使用したコマンドの実行は、SELinuxなどのオペレーティングシステムのアクセス制御メカニズムによって制限される場合があります。

COPY FROMは、ターゲットテーブルのトリガーとチェック制約を呼び出します。 ただし、ルールは呼び出されません。

ID列の場合、COPY FROMコマンドは、INSERTオプションOVERRIDING SYSTEM VALUEのように、入力データに指定された列値を常に書き込みます。

COPY入出力はDateStyleの影響を受けます。 デフォルト以外のDateStyle設定を使用する可能性のある他のPolarDBインストールへの移植性を確保するには、COPY Toを使用する前にDateStyleをISOに設定する必要があります。 また、IntervalStyleをsql_standardに設定してデータをダンプしないことをお勧めします。これは、IntervalStyleの設定が異なるサーバーでは、負の間隔値が誤って解釈される可能性があるためです。

入力データは、ENCODINGオプションまたは現在のクライアントエンコーディングに従って解釈され、出力データは、データがクライアントを通過せずにサーバーによって直接ファイルから読み取りまたはファイルに書き込まれる場合でも、encodingまたは現在のクライアントエンコーディングでエンコードされます。

COPYは最初のエラーで操作を停止します。 これにより、COPY toの場合に問題が発生することはありませんが、ターゲットテーブルはすでにCOPY FROMの以前の行を受信しています。これらの行は表示またはアクセスできませんが、ディスクスペースを占有します。 これは、大きなコピー操作で障害が発生した場合、かなりの量のディスクスペースが無駄になる可能性があります。 無駄なスペースを回復するには、VACUUMを呼び出します。

FORCE_NULLとFORCE_NOT_NULLは、同じ列で同時に使用できます。 これにより、引用されたnull文字列がnull値に変換され、引用されていないnull文字列が空の文字列に変換されます。

ファイル形式

テキスト形式

テキスト形式を使用する場合、読み書きされるデータは、テーブル行ごとに1行のテキストファイルになります。 行内の列は区切り文字で区切られます。 列値自体は、各属性のデータ型の出力関数によって生成された文字列、または入力関数に受け入れられる文字列です。 指定されたnull文字列は、nullの列の代わりに使用されます。 COPY FROMは、入力ファイルのいずれかの行に含まれる列が予想よりも多いまたは少ない場合にエラーを発生させます。

データの終わりは、バックスラッシュ期間 (\.) だけを含む1行で表すことができます。 ファイルの終わりは完全にうまく機能するため、ファイルから読み取るときにはデータの終わりのマーカーは必要ありません。これは、pre-3.0のクライアントプロトコルを使用してクライアントアプリケーションとの間でデータをコピーするときにのみ必要です。

COPYデータでバックスラッシュ文字 (\) を使用して、行または列の区切り文字として使用できるデータ文字を引用できます。 特に、列値の一部として表示される場合、次の文字の前にバックスラッシュを付ける必要があります。バックスラッシュ自体、改行、キャリッジリターン、および現在の区切り文字です。

指定されたnull文字列は、バックスラッシュを追加せずにCOPY TOによって送信されます。逆に、COPY FROMは、バックスラッシュを削除する前に、入力をnull文字列と照合します。 したがって、\Nなどのnull文字列は、実際のデータ値 \N (\\Nとして表される) と混同することはできません。

次の特別なバックスラッシュシーケンスは、COPY FROMによって認識されます。

現在、COPY TOは8進または16進のバックスラッシュ・シーケンスを発することは決してないが、これらの制御文字に対して上記の他のシーケンスを使用する。

上記の表に記載されていない他の文字は、それ自体を表すために使用されます。 ただし、バックスラッシュを不必要に追加することに注意してください。データ終了マーカー (\.) またはnull文字列 (デフォルトでは \N) に一致する文字列が誤って生成される可能性があるためです。 これらの文字列は、他のバックスラッシュ処理が行われる前に認識されます。

COPYデータを生成するアプリケーションは、データの改行とキャリッジがそれぞれ \nシーケンスと \rシーケンスに変換することを強くお勧めします。 現在、データ・キャリッジ・リターンをバックスラッシュおよびキャリッジ・リターンで表し、データ・ニューラインをバックスラッシュおよびニューラインで表すことが可能である。 ただし、これらの表現は将来のリリースでは受け入れられない場合があります。 また、COPYファイルが異なるマシン間で転送される場合 (たとえば、UnixからWindowsへ、またはその逆) 、破損に対して非常に脆弱です。

COPY TOは、Unixスタイルの改行 (「 \n」) で各行を終了します。 Microsoft Windowsで実行されているサーバーは、代わりにキャリッジリターン /改行 (" \r\n ") を出力しますが、サーバーファイルへのCOPYのみを出力します。プラットフォーム間の一貫性のため、COPY to STDOUTはサーバープラットフォームに関係なく常に "\n" を送信します。 COPY FROMは、改行、キャリッジリターン、またはキャリッジリターン /改行で終わる行を処理できます。 データとして意味されていたバックスラッシュされていない改行またはキャリッジリターンによるエラーのリスクを減らすために、入力の行末がすべて同じではない場合、COPY FROMは文句を言います。

CSV形式

この形式オプションは、スプレッドシートなどの他の多くのプログラムで使用されるコンマ区切り値 (CSV) ファイル形式をインポートおよびエクスポートするために使用されます。 PostgreSQLの標準テキスト形式で使用されるエスケープルールの代わりに、一般的なCSVエスケープメカニズムを生成および認識します。

各レコードの値は、DELIMITER文字で区切ります。 値に区切り文字、QUOTE文字、NULL文字列、キャリッジリターン文字、または改行文字が含まれる場合、値全体の先頭にはQUOTE文字が付いています。QUOTE文字またはESCAPE文字の値の前にエスケープ文字が付いています。 FORCE_QUOTEを使用して、特定の列にNULL以外の値を出力するときに引用符を強制することもできます。

CSV形式には、NULL値と空の文字列を区別する標準的な方法はありません。 PostgreSQLのCOPYはこれを引用して処理します。 NULLはNULLパラメーター文字列として出力され、引用符で囲まれません。一方、NULLパラメーター文字列に一致する非NULL値は引用符で囲まれます。 たとえば、デフォルト設定では、NULLは引用符なしの空の文字列として書き込まれ、空の文字列データ値は二重引用符 ("") で書き込まれます。 値の読み取りも同様の規則に従います。 FORCE_NOT_NULLを使用して、特定の列に対するNULL入力の比較を防ぐことができます。 FORCE_NULLを使用して、引用されたnull文字列データ値をNULLに変換することもできます。

バックスラッシュはCSV形式の特殊文字 \. ではないため、データ終了マーカーもデータ値として表示される可能性があります。 誤解を避けるために、\. 1行に1つのエントリとして表示されるデータ値は、出力時に自動的に引用され、入力時に引用された場合、データ終了マーカーとして解釈されません。 引用符なしの列が1つあり、値が\.、入力ファイルでその値を引用する必要がある場合があります。

CSV形式では、すべての文字が重要です。 空白で囲まれた値、またはDELIMITER以外の文字には、これらの文字が含まれます。 空白のCSV行を一定の幅で埋めるシステムからデータをインポートすると、エラーが発生する可能性があります。 このような状況が発生した場合、データをPolarDBにインポートする前に、CSVファイルを前処理して末尾の空白を削除する必要があります。

CSV形式は、埋め込みキャリッジリターンと改行を含む引用値を持つCSVファイルを認識して生成します。 したがって、ファイルは厳密にテキスト形式のファイルのようにテーブル行ごとに1行ではありません。

多くのプログラムは奇妙で時折ひねくれたCSVファイルを生成するため、ファイル形式は標準よりも慣習です。 したがって、このメカニズムを使用してインポートできないファイルが発生し、COPYが他のプログラムで処理できないファイルを生成する可能性があります。

バイナリ形式

バイナリ形式オプションを使用すると、すべてのデータがテキストではなくバイナリ形式として保存 /読み取りされます。 テキスト形式やCSV形式よりも多少高速ですが、バイナリ形式のファイルは、マシンアーキテクチャとPostgreSQLversionsの間で移植性が低くなります。 また、バイナリ形式は非常にデータ型に固有です。たとえば、smallint列からバイナリデータを出力してinteger列に読み込むことはできません。

バイナリファイル形式は、ファイルヘッダー、行データを含む0個以上のタプル、およびファイルトレーラーで構成されます。 ヘッダーとデータはネットワークバイト順です。

ファイルヘッダー

ファイルヘッダは、15バイトの固定フィールドと、それに続く可変長ヘッダ拡張領域とからなる。 固定フィールドは次のとおりです。

Signature

11バイトのシーケンスPGCOPY\n\377\r\n\0 -ゼロバイトは署名の必須部分であることに注意してください。 (署名は、8ビットクリーンでない転送によって処理されたファイルの容易な識別を可能にするように設計される。 このシグネチャは、エンド・オブ・ライン変換フィルタ、ドロップされたゼロ・バイト、ドロップされた上位ビット、またはパリティの変更によって変更される。

フラグフィールド

ファイル形式の重要な側面を示す32ビット整数ビットマスク。 ビットは、0 (LSB) から31 (MSB) まで番号付けされる。 このフィールドは、ファイル形式で使用されるすべての整数フィールドと同様に、ネットワークバイト順 (最上位バイト順) で格納されることに注意してください。 ビット16〜31は、重大なファイルフォーマットの問題を示すために予約されています。リーダーは、この範囲で予期しないビットセットを見つけた場合、中止する必要があります。 ビット0〜15は、後方互換性のあるフォーマットの問題を通知するために予約されています。リーダーは、この範囲に設定された予期しないビットを無視してください。 現在、1つのフラグビットのみが定義されており、残りはゼロでなければなりません。

ビット16

1の場合はOIDがデータに含まれ、0の場合は含まれません。 Oidシステム列はPolarDBではサポートされなくなりましたが、形式には引き続きインジケーターが含まれます。

ヘッダー拡張エリアの長さ

32ビット整数、ヘッダーの残りのバイト単位の長さ、自己を含まない。 現在、これはゼロであり、最初のタプルがすぐに続く。 今後のフォーマットの変更により、追加のデータがヘッダーに存在する可能性があります。 リーダーは、何をすべきかわからないヘッダー拡張データを黙ってスキップする必要があります。

ヘッダ拡張領域は、自己識別チャンクのシーケンスを含むように想定される。 フラグフィールドは、拡張領域に何があるかを読者に知らせることを意図していない。 ヘッダー拡張コンテンツの特定のデザインは、後のリリースのために残されます。

この設計は、後方互換ヘッダ追加 (ヘッダ拡張チャンクの追加、または下位フラグビットの設定) および非後方互換変更 (上位フラグビットを設定してそのような変更を通知し、必要に応じて拡張領域にサポートデータを追加する) の両方を可能にする。

タプル

各タプルは、タプル内のフィールドの数の16ビット整数カウントで始まる。 (現在、テーブル内のすべてのタプルは同じカウントを有するが、それは常に真であるとは限らない。) 次に、タプル内の各フィールドについて繰り返されると、32ビット長のワードが存在し、その後にフィールドデータのその多くのバイトが続く。 (長さワードはそれ自体を含まず、ゼロとすることができる。) 特殊な場合として、− 1はNULLフィールド値を示す。 NULLの場合、値バイトは続かない。

フィールド間に位置合わせパディングやその他の余分なデータはありません。

現在、バイナリフォーマットファイル内のすべてのデータ値は、バイナリフォーマット (フォーマットコード1) であると仮定される。 将来の拡張は、カラムごとのフォーマットコードが指定されることを可能にするヘッダフィールドを追加し得ることが予想される。

OIDがファイルに含まれている場合、OIDフィールドはフィールド・カウント・ワードの直後に続く。 これは、フィールドカウントに含まれていないことを除いて、通常のフィールドです。 oidシステム列は、現在のバージョンのPolarDBではサポートされていません。

ファイルトレーラー

ファイルトレーラは、− 1を含む16ビット整数ワードからなる。 これは、タプルのフィールド・カウント・ワードと容易に区別される。

フィールド・カウント・ワードが − 1でもなく、予想される列数でもない場合、リーダはエラーを報告すべきである。 これは、何らかの形でデータと同期しなくなることに対する追加のチェックを提供します。

例

次の例では、縦バー (|) をフィールド区切り文字として使用して、テーブルをクライアントにコピーします。

国をSTDOUTにコピー (DELIMITER '|');

ファイルからcountryテーブルにデータをコピーするには:

COPY country FROM '/usr1/proj/bray/sql/country_data';

名前が 'A' で始まる国だけをファイルにコピーするには:

COPY (SELECT * FROM country_name LIKE 'A % ') TO'/usr1/proj/bray/sql/a_list_countries.copy ';

圧縮ファイルにコピーするには、外部圧縮プログラムを介して出力をパイプすることができます。

COPY国をプログラム 'gzip > /usr1/proj/bray/sql/country_data.gz ';

STDINからテーブルにコピーするのに適したデータのサンプルを次に示します。

AFアフガニスタン
    アルアルバニア
    DZアルジェリア
    ZMザンビア
    ZWジンバブエ 

以下は同じデータで、バイナリ形式で出力されます。 Unixユーティリティod -cでフィルタリングした後のデータを示します。 テーブルには3つの列があります。1つ目はchar(2) 型、2つ目はtext型、3つ目はinteger型です。 すべての行の3番目の列にnull値があります。

0000000   P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \0
    0000020 \0 \0 \0 \003 \0 \0 \0 002 A F \0 \0 \0 013 A
    0000040 F G H A N I S T A N 377 377 377 377 \0 003
    0000060 \0 \0 \0 002 A L \0 \0 007 A L B A N I
    0000100 A 377 377 377 377 \0 \0 \0 \0 002 D Z \0 \0 \0
    0000120 007 A L G E R A 377 377 377 377 \0 003 \0 \0
    0000140 \0 002 Z M \0 \0 006 Z A M B I 377 377
    0000160 377 377 \0 003 \0 \0 \0 002 Z W \0 \0 \0 \b Z I
    0000200 M B A B W E 377 377 377 377 377 377