Postgresql – Does PG somehow support the ability to set a column to “variable type”

postgresql

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

Best Answer

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.

Related Solutions

PostgreSQL: DBLink weird permission/connection error

Here is what I think is happening. I don't think it is a bug.

You have a view. That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)

My recommendations:

Wrap the whole result set in a plpgsql function. Wrap that function in the view. OR
Use a WITH clause. I am less sure about this one. I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions. Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output! Let's skip and only run the second portion!" This is a feature, not a bug (and it is what allows table partitioning to be useful).

Postgresql – Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

If you're looking for alternatives other than JSON, and you just need arrays of text name-value pairs collections, you could simply use PostgreSQL's arrays:

CREATE TYPE mydoc AS (content_type text, names text[], values text[]);
CREATE TABLE documentData(id int primary key, documentName text, docs mydoc[]);
INSERT INTO documentData(id,documentName,docs) VALUES (1, 'DocumentA', '{"(Table,\"{ColA,ColB,ColC}\",\"{val1,val2,val3}\")","(Table,\"{ColA,ColB,ColC}\",\"{val4,val5,val6}\")"}'::mydoc[])

SELECT d.id, d.documentName, dd.*
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true);

This won't get a significant advantage over JSON, other than portability to older PostgreSQL versions, but unnest and =ANY type of operations are often much simpler than the JSON functions, so it may be easier to query and manipulate, e.g.:

SELECT d.id, d.documentName, dd.content_type, pos, name, dd.values[pos] AS value
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true)
LEFT JOIN LATERAL unnest(dd.names) WITH ORDINALITY AS y (name, pos) ON (true)
WHERE dd.content_type = 'Table' AND name ~ '^Col[AB]';

Best Answer

Related Solutions

PostgreSQL: DBLink weird permission/connection error

Postgresql – Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

Related Question