problems with timestamp

Anon

Hi.

I've been using PostgreSQL 7.1.3 for a year without problems.
Last week I installed a new server with Red Hat 7.2 and PostgreSQL 7.2.1
I've copied my database by using "pg_dumpall" from my old server to the new one.

But now the following query doesn't work:

SELECT date_part('year', fechaacta) AS "Año de donación", sexo AS "Sexo", count(*) AS "Cantidad de donantes" FROM "Capital" WHERE date_part('year', age(timestamp(fechanacimiento))) BETWEEN 1 AND 100
AND "fecha" BETWEEN '2001/01/01' AND '2002/12/31' GROUP BY date_part('year', fechaacta), sexo

the answer from server was:

Warning: PostgreSQL query failed: ERROR: parser: parse error at or near "fechanacimiento" in /var/www/html/ActasPcias/consultas.php on line 471

note: I've taken a care that "fechanacimiento" was a field of table "Capital".

Exactly the same query in exactly the same database works perfectly on my old server

Can anybody tell me what's going on ?

Thank.

Juan Pablo

slug58

are you sure you are using the "age()" function properly?

could it maybe be this instead:

age('year', date_part(timestamp(fechanacimiento))) BETWEEN 1 AND 100

hope tis helps some what!

Anon

If you take the time to read the Postgres 7.2 release notes before upgrading, you'll notice that the timestamp syntax was changed to be sql compliant (ie. timestamps now have milliseconds)

This means you must now quote your functions:

SELECT date_part('year', fechaacta) AS "Año de donación", sexo AS "Sexo", count(*) AS "Cantidad de donantes" FROM "Capital" WHERE date_part('year', age("timestamp"(fechanacimiento))) BETWEEN 1 AND 100
AND "fecha" BETWEEN '2001/01/01' AND '2002/12/31' GROUP BY date_part('year', fechaacta), sexo

...and it will work fine.