Re: INTERVAL data type and libpq - what format?

On Tue, May 19, 2009 at 10:08:37AM +0200, Sebastien FLAESCH wrote:
> I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
> the input formatting rules...

I think you're giving the database conflicting instructions and it's
getting confused.

> fprintf(stdout,"++ Preparing INSERT ...\n");
> paramTypes[0] = 23; /* INT4 */
> paramTypes[1] = 1186; /* INTERVAL */
> paramTypes[2] = 1186; /* INTERVAL */

I don't really know 8.4, but I believe you're saying here that you
explicitly want the values to be of basic INTERVAL type here, i.e. not
INTERVAL DAY TO HOUR for parameter 3.

Thus when you do:

> paramValues[0] = "1";
> paramValues[1] = "-12345 years";
> paramValues[2] = " 123 11:00";
> r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);

It's interpreting " 123 11:00" correctly as a basic INTERVAL value and
then casting it to your more constrained version as you're saving in the
table.

However, when you do:

> paramValues[0] = "2";
> paramValues[1] = "-12345";
> paramValues[2] = " 123 11";
> r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);

You get an error because " 123 11" isn't a valid literal of an
(undecorated) INTERVAL type. I think PG may do the right thing if you
don't specify the types when preparing the query, but haven't tested.

--
Sam http://samason.me.uk/