Forum OpenACS Q&A: postgresql syntax

I'm trying to get News working on 4.6.3/pg7.3.3 - There are currently 6 bugs in bugtracker. The first one comes up when trying to create a new post and seems to be related to

select current_timestamp + 14 from dual

The 14 comes from a call to [ad_parameter ActiveDays "news" 14]. The error reported is a casting issue - can't add an integer to a timestamp. I've been reviewing the postgres docs and searching online but haven't been able to find the proper syntax for adding days to a date. I tried casting to interval but that didn't work, can't cast to day/days/hours. I'm stumped so far. I'm sure it's joke-simple so instead of wasting more time searching, perhaps someone could help me out?

Thanks Jarkko. But no dice - typing into psql for 7.3.3 gives this error:

 Unable to identify an operator '+' for types 'timestamptz' and 'integer'
        You will have to retype this query using an explicit cast
physworx=# Unable to identify an operator '+' for types 'timestamptz' and 'integer'

Could this be related to the changes in timestamp from 7.2 to 7.3?

Brad,

You got it. This changed in PG 7.3

See this bug report and patch for information on fixing it.

https://openacs.org/bugtracker/openacs/bug?filter%2estatus=closed&bug%5fnumber=497

If you wanted to keep to the new Postgres standards that they are trying to achieve use the current_timestamp instead of now(). I believe they will be phasing now() out eventually

You can use it as follows for all 7.3.x releases

SELECT current_timestamp + '14 days'::INTERVAL from dual;

Unless current_timestamp is a field in the table dual though you could just do the following

SELECT current_timestamp + '14 days'::INTERVAL;

HTH

Thanks all!

Seems my efforts to cast to interval were the right direction, just screwed
up the syntax - the docs make it look like it should be + interval('14
days') where it has to be just interval '14 days' or '14 days'::interval, latter method preferred I take it?

Following through to the patch in the above link, it seems that at least the
first bug was already fixed for news. But the bug in the above patch isn't
in the news package section of bug tracker - is there much duplication
within bug tracker i.e. might the other news package bugs already be fixed
elsewhere?

Also, once I make changes on my site in the package contents like the xql
files, do I need to restart the server to see them? i.e. are all the xql
files for active packages cached on startup?

Once I get everything fixed up with news (and I'm sure it will involve a bunch more postings...), I'll talk to Don about committing them.