Postgres LATERAL - TIL

I had never heard about Postgres LATERAL before, until I saw it in my colleague's code. I spent some times doing researches to see how it worked. If you are reading this post, I would say that LATERAL will become super helpful for you.

What is LATERAL?

This is one query with LATERAL you might see somewhere:

SELECT u.id as user_id, u.name as user_name,
pu.title as post_title, pu.created_at as post_created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT * FROM posts p
  WHERE p.user_id = u.id ORDER BY p.created_at LIMIT 1
) pu on true;

Basically, this query is to query all from users, with each user, we will get their 1 latest posts. Without LATERAL, we cannot reference to u.id inside subquery. With LATERAL, subquery can reference any columns which is introduces before FROM. It can be visualized like this:

users u
  -> for each user
      -> run subquery that depends on u

You can read more on official docs (opens in a new tab) right here

Use cases

Since LATERAL can be very useful in some cases:

• explode array/json per row
• get Top N results (in the example above I get top 1 posts of users)
• ...

For example, we can do LATERAL with unnest array. Let's add one more field into users table

ALTER TABLE users
ADD COLUMN "hobbies" TEXT[] null;

Example data:

Use LATERAL to unnest array:

SELECT u.id, u.name, hobby from users u
LEFT JOIN LATERAL unnest(u.hobbies) as hooby on true;

Also, you can check out this post (opens in a new tab) to see how we can replace CTE with LATERAL to boost up query performance.

References

• Postgres' lateral join, have you heard about it? (opens in a new tab)
• PostgreSQL (opens in a new tab)

Thank you for reading. Welcome all your comments/feedbacks.