Does PG somehow support the ability to set a column to "variable type"?

Question

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".

Answer 1

AFAIK know there is no such union type. I would consider this a last resort, but here is a sketch if you truly need such type:

CREATE TABLE T
( attribute_key text not null
, attribute_value text not null
, attribute_type smallint not null -- example 1=int, 2=...
);

You can add a type check like:

attribute_type = 1 => attribute_value ~ '^[0-9]+'

and so forth. The above rule would be:

CHECK( attribute_type <> 1 or attribute_value ~ '^[0-9]+' );

The other option I could think of is to use XML to store the value. You can create a schema for the data types you are supporting, and validate against that schema during insert.