Shall I use JSONB for integers based array?

Question

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?

• Vérace: 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 :-)
• Craig Ringer: 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.

Answer 1

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.

• Mohammad AL-Raoosh: What do you mean by the recommended storage is always normalised?
• MTCoster: @MohammadAL-Raoosh Presumably a reference to database normalisation, a method of exploiting the functionality of the RDBMS to achieve increased efficiency
• IMSoP: @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.
• MTCoster: @IMSoP Good point! Apparently I've got the whole thing backwards in my head somehow