From: | Brandon Metcalf <brandon(at)geronimoalloys(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>, Jasen Betts <jasen(at)xnet(dot)co(dot)nz>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: quoting values magic |
Date: | 2009-05-26 20:09:17 |
Message-ID: | Pine.LNX.4.58L.0905261507290.17654@cedar.geronimoalloys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
t == tgl(at)sss(dot)pgh(dot)pa(dot)us writes:
t> Brandon Metcalf <brandon(at)geronimoalloys(dot)com> writes:
t> > d == dalroi(at)solfertje(dot)student(dot)utwente(dot)nl writes:
t> > d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
t> > d> > The issue here is that these reduce back to my original problem. For
t> > d> > example, if I use a CASE statement and I fall through to the ELSE,
t> > d> > then the SQL is attempting to insert a "''" in a NUMERIC field which
t> > d> > is not valid. That is, it's trying to do
t> > d> No it doesn't, read that statement again ;)
t> > Oops. Indeed, you are correct.
t> I think there is a problem though. If you have
t> case when '$length'='' then length else '$length' end
t> then what the parser is going to see is a CASE expression with a
t> variable (known to be NUMERIC) in one arm and an unknown-type literal
t> constant in the other arm. So it's going to decide that the literal
t> must be NUMERIC too, and that type coercion will fail if the literal
t> is really just ''.
t> Some experimentation suggests that you might get away with
t> case when '$length'='' then length else '$length'::text::numeric end
I think this is what I meant to say :) If $length contains a number,
then the resulting statement will be $length = '5.8', for example, and
this will fail for type NUMERIC.
--
Brandon
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2009-05-26 22:19:28 | Re: Need beginning and ending date value for a particular week in the year |
Previous Message | Conrad Lender | 2009-05-26 19:37:37 | Re: Code tables, conditional foreign keys? |