How to Update Specific Postgres Rows from a CSV File?

Using CSV Data to Update Specific Postgres Rows

This guide demonstrates how to efficiently update selected rows in a PostgreSQL table using data from a CSV file. We'll leverage the COPY and UPDATE commands for this task.

Scenario:

Imagine a PostgreSQL table, 'tbl', with columns 'id', 'banana', and 'apple'. The goal is to update only the 'banana' column for specific rows, based on information in a CSV file.

Procedure:

The solution involves these steps:

1. Create a Temporary Table:

   Construct a temporary table, tmp_x, mirroring the structure of the 'tbl' table:

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

2. Import CSV Data:

   Import the CSV file's contents into tmp_x using the COPY command. 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. Update the Main Table:

   Execute an UPDATE statement to modify the 'banana' column in 'tbl'. This uses data from tmp_x, matching rows based on the 'id' column:

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

4. Remove the Temporary Table:

   After the update, remove the temporary table:

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

Alternative Approaches:

• Simplified Temporary Table Creation: If your CSV file matches 'tbl''s structure, you can create tmp_x more concisely:

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

• Avoiding Superuser Privileges: For COPY operations without superuser rights, use the psql meta-command:

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

Helpful Links:

• PostgreSQL COPY Command
• PostgreSQL UPDATE Command
• PostgreSQL Temporary Tables

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