patternsqlMinor
Shall I use JSONB for integers based array?
Viewed 0 times
arrayforbasedshallintegersjsonbuse
Problem
I need to store a list of ids in array per each row.
I thought to use JSONB, then store it as :
Then, I found
The question is, what is the recommended data type for my case?
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?
Solution
Unless you have a very good reason to do otherwise, the recommended storage is always normalised.
Store the integers in a separate table of
If you must use arrays, use PostgreSQL's native arrays
Storing them as
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.Context
StackExchange Database Administrators Q#166245, answer score: 7
Revisions (0)
No revisions yet.