Menu
It seems to me that the functionality of the PostgreSQL array datatype overlaps a lot with the standard one-to-many and many-to-many relationships.
For example, a table called users could have an array field called "favorite_colors", or there could be a separate table called "favorite_colors" and a join table between "users" and "favorite_colors".
In what cases is the array datatype OK to use instead of a full-blown join?
915
PostgreSQL arrays are very powerful, and GIN indexing support makes them efficient to work with. Nonetheless, it's still not so efficient that you would replace a lookup table with an array in situations where you do a lot of lookups, though.
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
PostgreSQL Array type PL/pgSQL in PostgreSQL allows us to declare a variable as an ARRAY type. This ARRAY can be either a base or a custom type. For example, if we want to store a list of PINCODE values, then, we can declare the variable as v_pincode INT[].
In PostgreSQL, you can create an array for any built-in or user-defined data type. However, an array can only contain one data type. This means you can have an array of strings, an array of integers, and the like, but you cannot have an array that has both integer and string types.
An array should not be used similar to a relation. It should rather contain indexed values that relate to one row very tightly. For example if you had a table with the results of a football match, than you would not need to do
id team1 team2 goals1 goals2 but would do
id team[2] goals[2] Because in this example, most would also consider normalizing this into two tables would be silly.
So all in all I would use it in cases where you are not interested in making relations and where you else would add fields like field1 field2 field3.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
