I am dealing with some polymorphic data: sometimes I may have an
integer, sometimes a floating point number, sometimes a byte, etc.
Eventually I may segregate each of these different data types into its
own table, but for now it's convenient to have them all in one place.
My plan is to use two columns: a "kind" column which tells me what kind
of data I have, and a "value" column containing a textual representation
of a value of the appropriate kind. Thus, for each possible value of
"kind", there are certain requirements on what kind of value text is
acceptable. I'd like to encode that as a checkable constraint. For
example:
CHECK(
CASE kind
WHEN 1 THEN value::int BETWEEN 0 AND 255 -- unsigned byte
WHEN 2 THEN value::int BETWEEN -128 AND 127 -- signed byte
...
ELSE false
END
)
That's easy enough to do for small integer intervals as above. But for
things like floating point numbers, there's no real limit; I just want
to ensure that the value would be convertable to some value of "double
precision" type if such a conversion were attempted. I could write my
own regular expressions to match the syntax of valid floating point
numbers, but that strikes me as an inelegant duplication of the logic
that is already present in PostgreSQL for doing such conversions.
Is there any way to use the type converters as predicates? Given a
chunk of text and a type, I want "true" if and only if the given text
would be parsible into some arbitrary value of the given type. If
parsing would fail, I don't want an error; I just want a boolean "false".
Can it be done?
