0

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

0

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.