[解決済み] PostgresでCSVファイルの値で選択した行を更新するには？

質問

Postgres を使用していて、CSV ファイルから取得する大きな更新クエリを作成したいと思っています。 (id, banana, apple) .

リンゴではなくバナナを変更するアップデートを実行したいのですが、新しいバナナとそのIDはそれぞれCSVファイルに格納されるでしょう。

Postgresのサイトを見てみましたが、例題が死ぬほど多いです。

どのように解決するのですか？

COPY というファイルを一時的にステージングテーブルに置き、そこから実際のテーブルを更新します。みたいな感じ。

CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- but see below

COPY tmp_x FROM '/absolute/path/to/file' (FORMAT csv);

UPDATE tbl
SET    banana = tmp_x.banana
FROM   tmp_x
WHERE  tbl.id = tmp_x.id;

DROP TABLE tmp_x; -- else it is dropped at end of session automatically

インポートされたテーブルが更新されるテーブルと正確に一致する場合、これは便利かもしれません。

CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;

既存のテーブルの構造に一致する、制約のない空の一時テーブルを作成します。

特権

Postgres 10まで、SQL COPY を使用するにはスーパーユーザ権限が必要です。

Postgres 11 以降では、さらにいくつかの 定義済みのロール (以前の "デフォルトロール") を許可するようになりました。 マニュアルを

COPY ファイルやコマンドの名前を指定できるのは、データベースのスーパーユーザ または以下のロールのいずれかを付与されたユーザー pg_read_server_files , pg_write_server_files または pg_execute_server_program [...]

その psql メタコマンド \copy はどのDBロールでも動作します。 マニュアルです。

フロントエンド(クライアント)コピーを実行します。これは SQL COPY コマンドを使用しますが、サーバーが指定されたファイルを読み書きする代わりに 指定されたファイルをサーバが読み書きする代わりに、psql がファイルを読み書きし、データをルーティングします。 は、サーバーとローカルファイルシステムとの間で行われます。これは、ファイル アクセス権および特権は、サーバーではなくローカルユーザーのものであることを意味します。 SQLスーパーユーザー特権は必要ありません。

テンポラリテーブルの範囲は、1つの セッション に限定されるので、上記は同じ psql セッションで実行されなければなりません。

CREATE TEMP TABLE ...;
\copy tmp_x FROM '/absolute/path/to/file' (FORMAT csv);
UPDATE ...;

これをbashコマンドでスクリプト化する場合は、必ずすべてを シングル psql 呼び出しでラップしてください。のように。

echo 'CREATE TEMP TABLE tmp_x ...; \copy tmp_x FROM ...; UPDATE ...;' | psql

通常はメタコマンドである \\ を使用して psql のメタコマンドと SQL コマンドを切り替えますが \copy は例外です。 またまたマニュアルです。

には特別な解析ルールが適用されます。 \copy メタコマンドに適用されます。他の多くのメタコマンドとは異なり、行の残りの部分全体は常に \copy の引数とみなされ、引数の中では変数補間もバッククオート展開も行われません。

大きなテーブル

インポートテーブルが大きい場合、インポートテーブルのサイズを大きくする必要がある場合があります。 temp_buffers をセッションのために一時的に使用します（セッションの最初）。

SET temp_buffers = '500MB';  -- example value

テンポラリーテーブルにインデックスを追加します。

CREATE INDEX tmp_x_id_idx ON tmp_x(id);

そして ANALYZE 一時テーブルは autovacuum / auto-analyze の対象外であるため、手動で実行します。

ANALYZE tmp_x;

関連する回答

• 数百万の行を ID で削除する最良の方法
• 異なるスキーマから一時テーブルに共通のデータを挿入するにはどうすればよいですか？
• 重複したエントリを削除するには？