Running Postgres 7.4 (Yeah we are in the midst of upgrading)
I need to store from 1 to 100 selected items into one field in a database. 98% of the time it's just going to be 1 item entered, and 2% of the time (if that) there will be multiple items.
The items are nothing more than a text description, (as of now) nothing more than 30 characters long. They are static values the user selects.
Wanted to know the optimal column data type used to store the desired data. I was thinking BLOB but didn't know if this is a overkill. Maybe JSON?
Also I did think of ENUM but as of now I can't really do this since we are running Postgres 7.4
I also wanted to be able to easily identify the item(s) entered so no mappings or referencing tables.
This question is related to
sql
arrays
postgresql
types
Well, there is an array type in recent Postgres versions (not 100% about PG 7.4). You can even index them, using a GIN or GIST index. The syntaxes are:
create table foo (
bar int[] default '{}'
);
select * from foo where bar && array[1] -- equivalent to bar && '{1}'::int[]
create index on foo using gin (bar); -- allows to use an index in the above query
But as the prior answer suggests, it will be better to normalize properly.
Source: Stackoverflow.com