type convertability as a checkable constraint
От | Ben Liblit |
---|---|
Тема | type convertability as a checkable constraint |
Дата | |
Msg-id | 3D35FC97.7050307@eecs.berkeley.edu обсуждение исходный текст |
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: