How to update selected rows with values from a CSV file in Postgres?

by

COPY the file to a temporary staging table and update the actual table from there. Like:

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

If the imported table matches the table to be updated exactly, this may be convenient:

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

Creates an empty temporary table matching the structure of the existing table, without constraints.

Privileges

Up to Postgres 10, SQL COPY requires superuser privileges for this.
In Postgres 11 or later, there are also some predefined roles (formerly “default roles”) to allow it. The manual:

COPY naming a file or command is only allowed to database superusers
or users who are granted one of the roles pg_read_server_files,
pg_write_server_files, or pg_execute_server_program […]

The psql meta-command \copy works for any db role. The manual:

Performs a frontend (client) copy. This is an operation that runs an
SQL COPY command, but instead of the server reading or writing the
specified file, psql reads or writes the file and routes the data
between the server and the local file system. This means that file
accessibility and privileges are those of the local user, not the
server, and no SQL superuser privileges are required.

The scope of temporary tables is limited to a single session of a single role, so the above has to be executed in the same psql session:

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

If you are scripting this in a bash command, be sure to wrap it all in a single psql call. Like:

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

Normally, you need the meta-command \\ to switch between psql meta commands and SQL commands in psql, but \copy is an exception to this rule. The manual again:

special parsing rules apply to the \copy meta-command. Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments.

Big tables

If the import-table is big it may pay to increase temp_buffers temporarily for the session (first thing in the session):

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

Add an index to the temporary table:

CREATE INDEX tmp_x_id_idx ON tmp_x(id);

And run ANALYZE manually, since temporary tables are not covered by autovacuum / auto-analyze.

ANALYZE tmp_x;

Related answers:

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. How to check if a table exists in a given schema
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Save PL/pgSQL output from PostgreSQL to a CSV file
  5. Optimise PostgreSQL for fast testing
  6. Transpose latest rows per user to columns
  7. How to delete duplicate rows without unique identifier
  8. Best way to check for “empty or null value”
  9. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  10. PostgreSQL parameterized Order By / Limit in table function
  11. How can I send email from PostgreSQL trigger?
  12. NOT NULL constraint over a set of columns
  13. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  14. How do I alter the position of a column in a PostgreSQL database table?
  15. Query that ignore the spaces
  16. How to find a table having a specific column in postgresql
  17. Continuing a transaction after primary key violation error
  18. INSERT COMMAND :: ERROR: column “value” does not exist
  19. Use email address as primary key?
  20. Bulk/batch update/upsert in PostgreSQL
  21. SQL query to find record with ID not in another table
  22. How to show row numbers in PostgreSQL query?
  23. Postgres Error: More than one row returned by a subquery used as an expression
  24. Grant privileges for a particular database in PostgreSQL
  25. Upsert with a transaction
  26. How to compare dates in datetime fields in Postgresql?
  27. PostgreSQL how to create a copy of a database or schema?
  28. Temporal database design, with a twist (live vs draft rows)
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment