From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: INTERVAL data type and libpq - what format? |
Date: | 2009-05-19 14:12:03 |
Message-ID: | 20090519141203.GS22221@samason.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Tue, May 19, 2009 at 02:17:20PM +0200, Sebastien FLAESCH wrote:
> As you can do with ODBC's SQLBindParameter(), where you specify the C type,
> SQL type, precision/scale or length ...
> I believe this is important when it comes to data type conversion (for ex,
> when you want to insert a numeric/date/time into a char or the other way).
> => sort of cast, actually...
Tom sent a message, but it seems to have got lost somewhere. The
suggestion was to leave the paramTypes empty and just write the prepared
statement as:
INSERT INTO tbl (k,i1,i2) VALUES ($1::INT,$2::INTERVAL YEAR,$3::INTERVAL);
or similar. That way PG will be able to infer that $1 will be a literal
of integer type, $2 will be of INTERVAL YEAR and so on. In fact for
queries such as this I don't think you even need to put those casts in
there as PG will be able to figure out what you mean automatically (i.e.
it does a limited form of type inference).
> I known libpq functions like PQexecParams() or PQexecPrepared() have a
> paramFormats[] parameter to specify if the buffer will hold a string
> or the binary representation of the value... but that would not help
> much (I don't want to deal with internal structures!).
Yes, stay away from binary types if at all possible!
> Also: I still have the overflow issue with types like INTERVAL SECOND.
> => discussed in another thread "INTERVAL SECOND limited to 59 seconds?"
If I read it correctly; it's not overflow but truncation. In PG (I've
got no idea what the database you're comparing to does/what the spec
says it's supposed to do) when you you want a value of type "INTERVAL
SECOND" then PG is interpreting this as meaning I want a value of type
INTERVAL where all the fields apart from the seconds are zero. Whether
this is useful seems debatable, Richard's suggestion of creating a set
of custom types that do the right thing for you seems like the path of
least resistance.
--
Sam http://samason.me.uk/
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2009-05-19 14:30:26 | Re: INTERVAL data type and libpq - what format? |
Previous Message | Richard Huxton | 2009-05-19 13:54:42 | Re: my insertion script don't work |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2009-05-19 14:30:26 | Re: INTERVAL data type and libpq - what format? |
Previous Message | Robert Haas | 2009-05-19 14:09:15 | Re: Show method of index |