Re: quoting values magic

Brandon Metcalf <brandon(at)geronimoalloys(dot)com> writes:
> d == dalroi(at)solfertje(dot)student(dot)utwente(dot)nl 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