Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> A customer came to us with this request: a way to store "any" data
> in a column.
+1
More than once (usually in replication, interface, or audit
situations) I've had to create a table with one column each of a
number of different data types, only one of which will be used in
each row. It would make life much simpler for programming such
things if an "unknown" type could be stored. I could either cast
the types in the rows based on the related identifier column, but it
would be even nicer if they could be read "unknown" and pg_typeof or
something similar could be used to control flow based on the type.
As one example, only *last night* I created the following table as
part of the development of our shop's next generation of software.
(Yes I know about Slony, Londiste, and others -- we need to roll
something out which integrates with existing systems, without
production disruption, over the next few years. This does require a
custom solution.) Table "public.DbTranOpValue" Column | Type | Modifiers
----------------+-------------------+-----------countyNo | "CountyNoT" | not nullbackendPid | integer
| not nulltranStart | "TimestampT" | not nulllogRecordSeqNo | "LogRecordSeqNoT" | not nullcolumnName
| "ColumnNameT" | not nullisAfter | "BooleanT" | not nulltextValue | text
|intValue | bigint |numericValue | numeric |binaryValue | bytea
|booleanValue | "BooleanT" |dateValue | "DateT" |timeValue | "TimeT"
|timestampValue| "TimestampT" |
Indexes: "DbTranOpValue_pkey" PRIMARY KEY, btree ("backendPid", "tranStart", "logRecordSeqNo", "columnName")
I would much rather have had a "value" column of unknown type.
-Kevin