Postgres Arrays: JOIN table with matching ids

egorpavlikhin

Egor Pavlikhin

Posted on February 13, 2020

Postgres Arrays: JOIN table with matching ids

Postgres has a wonderful feature that allows you to store any data type as an array. You can expand this to represent a one-to-many relationship and use it like so

SELECT u.*
FROM unnest(ARRAY[31,32,410]) user_id
LEFT JOIN user u on u.id = user_id

Be aware of potential issues when working with arrays though. While they are good if you want to store point-in-time data where you "write and forget", maintaining such data model can be troublesome:

  • No foreign keys support for arrays
  • Limited support in ORMs and libraries
  • To update an array you need to re-write all of it
💖 💪 🙅 🚩
egorpavlikhin
Egor Pavlikhin

Posted on February 13, 2020

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related