I need to store a list of ids in array per each row. I thought to use JSONB, then store it as : {ids: [1, 2, 3, 4]}. Then, I found Array type, that I can use to store ids directly into. The question is, what is the recommended data type for my case?

  • Arrays are non-standard and a breach of the relational model. As @CraigRinger points out, it's well nigh impossible to join arrays to anything. IMHO, arrays in RDBMS's are the work of the devil :-) – Vérace Mar 5 '17 at 11:37
  • Joining on arrays isn't too bad in Pg. But it's not particularly efficient or easy to optimise, you can't use FKs, etc. – Craig Ringer Mar 5 '17 at 11:53

Unless you have a very good reason to do otherwise, the recommended storage is always normalised.

Store the integers in a separate table of (other_row_id, the_integer) and join on them.

If you must use arrays, use PostgreSQL's native arrays ARRAY[1,2,3].

Storing them as jsonb is a spectacularly inefficient and clumsy way to do it with essentially no advantages.

  • What do you mean by the recommended storage is always normalised? – Mohammad AL-Raoosh Mar 5 '17 at 12:47
  • @MohammadAL-Raoosh Presumably a reference to database normalisation, a method of exploiting the functionality of the RDBMS to achieve increased efficiency – MTCoster Mar 5 '17 at 13:02
  • 4
    @MTCoster I'd say efficiency is rather a side-effect of normalisation than its aim: modern DBMSes are optimised for normalised data because normalisation was seen as a good thing. The primary driving force for the invention of normalisation was consistency and integrity. – IMSoP Mar 5 '17 at 19:37
  • @IMSoP Good point! Apparently I've got the whole thing backwards in my head somehow – MTCoster Mar 6 '17 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.