Does PG somehow support the ability to set a column to "variable type"? I sometimes get into the situation where I wish to store &quot;arbitrary fields&quot; with their value. Rather than creating a table structure with tons of columns, and perpetually having to change this table as more &quot;fields&quot; are added, I have a table with basically just a &quot;field&quot; and &quot;value&quot; column, where both are of text type, meaning it can &quot;store any value&quot;, albeit in a less-than-optimal manner in terms of speed and &quot;deep knowledge&quot; 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 &quot;0&quot;/&quot;1&quot; or literal &quot;true&quot; or &quot;false&quot;. It just &quot;feels icky&quot;, 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 &quot;variable&quot; column type of some sort, so that it can dynamically become a &quot;boolean&quot; or &quot;text&quot; or &quot;date&quot; or &quot;integer&quot; depending on the value inserted/updated for the &quot;field&quot; 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 &quot;text columns that hold anything&quot;, but I always have the nagging feeling that &quot;this isn't quite the optimal or intended way to do things&quot;.
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 =&gt; attribute_value ~ '^[0-9]+'

and so forth. The above rule would be:
CHECK( attribute_type &lt;&gt; 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.