How to Update Specific Postgres Rows Using Data from a CSV File?

Updating Postgres Table Rows with CSV Data

This guide demonstrates how to efficiently update specific rows in a PostgreSQL table using data from a CSV file. The process involves several key steps:

First, create a temporary table to store the CSV data. The COPY command is used to import the CSV file into this temporary table. Ensure you replace /absolute/path/to/file with the actual path to your CSV file.

<code class="language-sql">CREATE TEMP TABLE tmp_data (id int, apple text, banana text);
COPY tmp_data FROM '/absolute/path/to/file' (FORMAT csv);</code>

Next, the UPDATE statement joins the main table (tbl) with the temporary table (tmp_data) based on the id column. This ensures that only the corresponding rows are updated.

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

Finally, remove the temporary table since it's no longer needed.

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

This method selectively updates rows, utilizing only the relevant data from the CSV. For larger tables, consider optimizing performance by indexing the temporary table and adjusting temporary buffer settings. Appropriate database privileges are also crucial for successful execution.

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