Bulk/batch update/upsert in PostgreSQL

by

Bulk insert

You can modify the bulk insert of three columns by @Ketema:

INSERT INTO "table" (col1, col2, col3)
  VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33);

It becomes:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(array[11,21,31]), 
          unnest(array[12,22,32]), 
          unnest(array[13,23,33]))

Replacing the values with placeholders:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(?), unnest(?), unnest(?))

You have to pass arrays or lists as arguments to this query. This means you can do huge bulk inserts without doing string concatenation (and all its hazzles and dangers: sql injection and quoting hell).

Bulk update

PostgreSQL has added the FROM extension to UPDATE. You can use it in this way:

update "table" 
  set value = data_table.new_value
  from 
    (select unnest(?) as key, unnest(?) as new_value) as data_table
  where "table".key = data_table.key;

The manual is missing a good explanation, but there is an example on the postgresql-admin mailing list. I tried to elaborate on it:

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) 
values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);

update tmp set age = data_table.age
from
(select unnest(array['keith', 'leslie', 'bexley', 'casey']) as name, 
        unnest(array[44, 50, 10, 12]) as age) as data_table
where tmp.name = data_table.name;

There are also other posts on StackExchange explaining UPDATE...FROM.. using a VALUES clause instead of a subquery. They might by easier to read, but are restricted to a fixed number of rows.

More Related Contents:

  1. How do I (or can I) SELECT DISTINCT on multiple columns?
  2. How to check if a table exists in a given schema
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Transpose latest rows per user to columns
  5. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  6. updating table rows in postgres using subquery
  7. How can I send email from PostgreSQL trigger?
  8. PostgreSQL multi INSERT…RETURNING with multiple columns
  9. SQL Between clause with strings columns
  10. Insert an image in postgresql database
  11. INSERT rows into multiple tables in a single query, selecting from an involved table
  12. Casting NULL type when updating multiple rows
  13. NOT NULL constraint over a set of columns
  14. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  15. Slow simple update query on PostgreSQL database with 3 million rows
  16. How do I alter the position of a column in a PostgreSQL database table?
  17. How to find a table having a specific column in postgresql
  18. postgresql: INSERT INTO … (SELECT * …)
  19. Continuing a transaction after primary key violation error
  20. INSERT COMMAND :: ERROR: column “value” does not exist
  21. Use email address as primary key?
  22. Postgres error updating column data
  23. How to show row numbers in PostgreSQL query?
  24. Postgres Error: More than one row returned by a subquery used as an expression
  25. Grant privileges for a particular database in PostgreSQL
  26. Update a column of a table with a column of another table in PostgreSQL
  27. Functions with variable number of input parameters
  28. Upsert with a transaction
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment