From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Florian Weimer <fw(at)deneb(dot)enyo(dot)de> |
Cc: | Andrew McNamara <andrewm(at)object-craft(dot)com(dot)au>, Greg Smith <greg(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Confusion over Python drivers |
Date: | 2010-02-09 04:32:26 |
Message-ID: | 1265689946.29919.2583.camel@jdavis |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, 2010-02-08 at 20:50 +0100, Florian Weimer wrote:
> I saw your note that you have to specify the types for date values
> etc. Is this really desirable or even necessary? Can't you specify
> the type as unknown (OID 705, I believe)?
I believe the problem that Andrew is describing is that:
SELECT $1 + 1;
will infer that $1 is of type int4. But if you really intended $1 to be
a date (which is also valid), it will cause a problem.
If the date is passed in text format, it will cause an error in
int4in(), because the text representation of a date isn't a valid text
representation for an integer.
If the date is passed in binary format, it will pass it to int4recv() --
but because the date is 4 bytes, and int4recv is defined for any 4-byte
input, it won't cause an error; it will produce a wrong result. In other
words, the binary representation for a date _is_ a valid binary
representation for an integer. The type inference has found the wrong
type, but the recv function still accepts it, which causes a problem.
The solution is to write the query in an unambiguous way:
SELECT $1::date + 1;
which is good practice, anyway. If it's not obvious to the type
inference system, it's probably not obvious to you, and will probably
surprise you ;)
Or, as Andrew suggests, you can pass the type oid along with the
parameter so that postgresql knows the right type.
Either way, relying on a type input or a recv function to cause a type
error is much more fragile.
Regards,
Jeff Davis
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2010-02-09 04:32:31 | Re: Hot standby documentation |
Previous Message | M Z | 2010-02-09 04:24:46 | Re: CVS checkout source code for different branches |