Photo by Kyle Petzer on Unsplash

Postgres UNNEST cheat sheet for bulk operations

originally posted on the @databases blog

Postgres is normally very fast, but it can become slow (or even fail completely), if you have too many parameters in your queries. When it comes to operating on data in bulk, UNNEST is the only way to achieve fast, reliable queries. This post has examples for using UNNEST to do all types of bulk transactions.

All the examples in this article assume a database schema that looks like:

CREATE TABLE users (
  email TEXT NOT NULL PRIMARY KEY,
  favorite_color TEXT NOT NULL
)

INSERT thousands of records in one go

To insert many records into a Postgres table in one go, the most efficient method is to provide each column as a separate array and then use UNNEST to construct the rows to insert.

You can run the following query:

INSERT INTO users (email, favorite_color)
SELECT * FROM UNNEST(?::TEXT[], ?::TEXT[])

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["red", "green", "indigo"]
]

Notice that you’re only passing 2 parameters, no matter how many rows you want to insert. You’re also using the same query text no matter how many rows you…