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.
answered Mar 5 '17 at 11:24
-
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
