Re: VARIANT / ANYTYPE datatype

Alvaro Herrera wrote:
> A customer came to us with this request: a way to store "any" data in a
> column. We've gone back and forth trying to determine reasonable
> implementation restrictions, safety and useful semantics for them.
> I note that this has been requested in the past:
> http://archives.postgresql.org/pgsql-general/2004-02/msg01266.php
> and both Oracle and MS-SQL have it and apparently people find them
> useful. I didn't find any indication that SQL contains anything
> resembling this.

I see VARIANT/ANYTYPE as the most general case of supporting union types, which,
say, could have more specific examples of "allow any number or date here but
nothing else". If VARIANT is supported, unions in general ought to be also.

The most effective way of supporting VARIANT or union types in general is having
an implementation where in the general case each value in the database knows its
own data type rather than the data type being provided by a context such as what
table column it is in. For example, if rather than storing a data value
directly we store a 2-attribute struct naming a data type and pointing to or
holding the data value.

See how SQLite works as an example of how VARIANTs or unions could work,
although that on its own would need to be made more comprehensive for Pg.

I claim ignorance as to how Pg currently implements these matters.

Where VARIANT/union types are supported by default, declaring more specific
types is just a type constraint and an optimization.

Of course, when we know the type of a column/etc isn't going to be VARIANT or
some other union type, then a simple optimization allows us to just store the
value and have its type provided by context rather than the struct.

-- Darren Duncan