How to Update Postgres Rows with Data from a CSV File?

Using CSV Data to Update Postgres Rows: A Step-by-Step Guide

This guide details how to update existing Postgres rows using data sourced from a CSV file. The process involves several key steps:

1. Establishing a Temporary Table

Begin by creating a temporary table within your Postgres database. This table will serve as a staging area for the CSV data. The table schema must mirror the target table's structure. For instance:

<code class="language-sql">CREATE TEMP TABLE tmp_x (id int, banana text, apple text);</code>

2. Importing CSV Data

Employ the COPY command to load the CSV file's contents into the temporary table. Remember to replace /absolute/path/to/file with the actual file path:

<code class="language-sql">COPY tmp_x FROM '/absolute/path/to/file' (FORMAT csv);</code>

3. Performing the Update

With the data in the temporary table, execute an UPDATE statement to modify the target table. This example updates the banana column based on matching id values:

<code class="language-sql">UPDATE tbl
SET    banana = tmp_x.banana
FROM   tmp_x
WHERE  tbl.id = tmp_x.id;</code>

4. Removing the Temporary Table

After the update is finalized, remove the temporary table to reclaim resources:

<code class="language-sql">DROP TABLE tmp_x;</code>

Important Notes:

• Access Permissions: Updating data with COPY generally requires superuser privileges or appropriately assigned roles (especially in Postgres 10 and later).
• Alternative for Non-Superusers: The copy meta-command within the psql client provides an alternative for users lacking superuser privileges.
• Performance Considerations: For large datasets, optimizing performance might involve increasing temp_buffers and indexing the temporary table.

This method offers an efficient way to update specific Postgres rows using data from a CSV file.

The above is the detailed content of How to Update Postgres Rows with Data from a CSV File?. For more information, please follow other related articles on the PHP Chinese website!