I sometimes get into the situation where I wish to store "arbitrary fields" with their value. Rather than creating a table structure with tons of columns, and perpetually having to change this table as more "fields" are added, I have a table with basically just a "field" and "value" column, where both are of text
type, meaning it can "store any value", albeit in a less-than-optimal manner in terms of speed and "deep knowledge" by the database engine about the data in the table.
For example, it's unclear if a boolean value sent from the application will be stored as a "0"/"1" or literal "true" or "false". It just "feels icky", although I don't consider having actual columns for every value to be a sensible alternative to this approach, which I consider to be even more problematic.
Since this is probably a very common situation, it struck me that PG might support a "variable" column type of some sort, so that it can dynamically become a "boolean" or "text" or "date" or "integer" depending on the value inserted/updated for the "field" column for a specific row? Or perhaps based on what I specify with ::whatever
?
Is this a thing? Is it stupid? I can work with my current system of "text columns that hold anything", but I always have the nagging feeling that "this isn't quite the optimal or intended way to do things".