Brandon Metcalf <brandon@xxxxxxxxxxxxxxxxxx> writes: > d == dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx writes: > d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote: > d> > The issue here is that these reduce back to my original problem. For > d> > example, if I use a CASE statement and I fall through to the ELSE, > d> > then the SQL is attempting to insert a "''" in a NUMERIC field which > d> > is not valid. That is, it's trying to do > d> No it doesn't, read that statement again ;) > Oops. Indeed, you are correct. I think there is a problem though. If you have case when '$length'='' then length else '$length' end then what the parser is going to see is a CASE expression with a variable (known to be NUMERIC) in one arm and an unknown-type literal constant in the other arm. So it's going to decide that the literal must be NUMERIC too, and that type coercion will fail if the literal is really just ''. Some experimentation suggests that you might get away with case when '$length'='' then length else '$length'::text::numeric end so that the text-to-numeric conversion is delayed to runtime. However this is a bit fragile (it's dependent on some undocumented details of the constant-expression-folding behavior) and it also requires hardwiring knowledge that length is indeed numeric into your SQL command. On the whole I'd suggest going with NULL, not empty string, as your representation of a missing update value if at all possible. Then the previously-suggested COALESCE solution will work, and you aren't relying on any shaky assumptions about when and how the parser will try to enforce validity of the datatype value. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
