DEV Community

Egor Pavlikhin
Egor Pavlikhin

Posted on • Originally published at pavlikhin.com

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

Top comments (0)