Postgres UNNEST Cheat Sheet for Bulk Operations

Postgres is usually very fast, but it can become slow (or even fail completely), if you have too many parameters in your queries. When it comes to working with data in bulk, UNNEST It is the only way to achieve fast and reliable inquiries. This post has examples of usage UNNEST To do all kinds of group 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
)

Enter thousands of records at once

To insert many records into a Postgres table at once, the most efficient way is to provide each column as a separate array and then use UNNEST To build rows for inclusion.

You can run the following query:

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

With parameters like:

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

Note that you are only passing two parameters, no matter how many rows you want to insert. You are also using the same query text no matter how many rows you want to insert. This is what keeps the query efficient.

The resulting table will look like this:

E-mail Favorite color
joe@example.com red
ben@example.com green
mary@example.com indigo

Update multiple records for different values ​​in one query

One of the strongest use cases UNNEST It is updating multiple records in a single query. natural UPDATE The statement only allows you to update multiple records at once only if you want to set them all to the same value, but this approach is more flexible.

You can run the following query:

UPDATE users
SET
  favorite_color=bulk_query.updated_favorite_color
FROM
  (
    SELECT
      UNNEST(?::TEXT[])
        AS email,
      UNNEST(?::TEXT[])
        AS updated_favorite_color
  ) AS bulk_query
WHERE
  users.email=bulk_query.email

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

The resulting table will then look like this:

E-mail Favorite color
joe@example.com purple
ben@example.com violet
mary@example.com orange

Not only does this let you update all of these records in a single statement, but the number of parameters remains constant at 2 now no matter how many rows you want to update.

Select with thousands of different conditions at once

You can always create a very large query by combining OR And AND, but in the end, if you have enough parameters, this may start to slow down.

You can run the following query:

SELECT * FROM users
WHERE (email, favorite_color) IN (
  SELECT
    UNNEST(?::TEXT[]),
    UNNEST(?::TEXT[])
)

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

It will be equivalent to running:

SELECT * FROM users
WHERE
  (email="joe@example.com" AND favorite_color="purple")
  OR (email="ben@example.com" AND favorite_color="violet")
  OR (email="mary@example.com" AND favorite_color="orange")

Utilization UNNEST Here it allows us to keep the query static, and use only two parameters, no matter how many conditions we want to add.

An alternative if you need more control could be to use INNER JOIN instead of IN part of the query. For example, if you need tests to be case insensitive, you can do the following:

SELECT users.* FROM users
INNER JOIN (
  SELECT
    UNNEST(?::TEXT[]) AS email,
    UNNEST(?::TEXT[]) AS favorite_color
) AS unnest_query
ON (LOWER(users.email) = LOWER(unnest_query.email) AND LOWER(user.favorite_color) = LOWER(unnest_query.favorite_color))

Delete thousands of different cases at once

exactly like SELECTAnd DELETE Queries can become slow if your circumstances become excessively complex.

You can run the following query:

DELETE FROM users
WHERE (email, favorite_color) IN (
  SELECT
    UNNEST(?::TEXT[]),
    UNNEST(?::TEXT[])
)

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

It will be equivalent to running:

DELETE FROM users
WHERE
  (email="joe@example.com" AND favorite_color="purple")
  OR (email="ben@example.com" AND favorite_color="violet")
  OR (email="mary@example.com" AND favorite_color="orange")

exactly like SELECT, Utilization UNNEST Here it allows us to keep the query static, and use only two parameters, no matter how many conditions we want to add.

If you are using node.js, you can do all these operations without having to memorize the syntax with @database/pg-typed or @database/pg-bulk.

.

Leave a Comment