Re: INTERVAL data type and libpq - what format?

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/