CSV ファイルから Postgres テーブルの行を効率的に更新するにはどうすればよいですか?

CSV データを使用した Postgres 更新の最適化

CSV ファイルのデータを使用して Postgres テーブルを効率的に更新することは、データ管理にとって非常に重要です。 テーブル (id、banana、apple) と、更新された banana 値を含む CSV ファイルがあるとします。 課題は、banana 列を変更せずに apple 列のみを更新することです。

COPY と UPDATE を活用して効率的なアップデートを行う

最適なアプローチには、COPY を使用して CSV を一時テーブルにインポートし、次に UPDATE 列に基づいて id を実行することが含まれます。プロセスは次のとおりです:

<code class="language-sql">CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- Or see alternative 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; -- Alternatively, it's automatically dropped at the end of the session</code>

一時テーブル構造がターゲットテーブルをミラーリングする場合のより効率的なアプローチについては、次のとおりです。

<code class="language-sql">CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;</code>

これにより、tbl と同じスキーマを使用して、制約を省略した空の一時テーブルが作成されます。

セキュリティとパフォーマンスに関する考慮事項

Postgres 11 より前では、COPY にはスーパーユーザー権限が必要でした。 ただし、Postgres 11 以降のバージョンでは、セキュリティを向上させるために事前定義されたロール (pg_read_server_files や pg_write_server_files など) が提供されています。

psql メタコマンド copy は、COPY コマンドをローカルで実行することで、スーパーユーザー権限の要件を回避する別の解決策を提供します。

大きな CSV ファイルの場合、パフォーマンスの最適化が重要です。 temp_buffers パラメータを一時的に増やすことを検討してください。 一時テーブルの id 列にインデックスを作成して ANALYZE を実行すると、クエリ速度をさらに向上させることができます。

以上がCSV ファイルから Postgres テーブルの行を効率的に更新するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。