Forum OpenACS Q&A: postgresql syntax

Posted by Brad Ford on

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?

2: Re: postgresql syntax (response to 1)
Posted by Jarkko Laine on
select now() + 14;
3: Re: postgresql syntax (response to 1)
Posted by Brad Ford on

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?

5: Re: postgresql syntax (response to 1)
Posted by Jarkko Laine on
ahh, sorry, I guess it was 7.2 I was trying that with.
4: Re: postgresql syntax (response to 1)
Posted by Dave Bauer on

You got it. This changed in PG 7.3

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

6: Re: postgresql syntax (response to 1)
Posted by Mat Kovach on
lack=# select (now() + INTERVAL '14 days') as time from dual;
2003-07-25 12:39:47.894596-04
(1 row)

lack@vhost:~$ psql -V
psql (PostgreSQL) 7.3.2
contains support for command-line editing

7: Re: postgresql syntax (response to 6)
Posted by Darren Ferguson on
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;


8: Re: postgresql syntax (response to 1)
Posted by Don Baccus on
Yes, please, use current_timestamp rather than switch to now(), which doesn't affect the type error in the expression.

PG used to treat integers in such expressions as being N days without the need to round off.  Also be wary of day roundoff issues in 7.2 it may be returning the date (i.e. no hour/minute/sec info) 14 days hence rather than 14*24 hours hence ...

Thanks for tackling these, Brad - e-mail me if you want cvs access to news directly and we can talk about getting your fixes into 4.6.4...

9: Re: postgresql syntax (response to 1)
Posted by Brad Ford on
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

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.