I have in my code a SQL statement that does the following:

select period_start + interval('1 hour') from periods;

This worked in 7.1, but in 7.2 I am getting the following error:

ERROR:  parser: parse error at or near "'"

If I remove the quotes then I get the following error:

ERROR:  parser: parse error at or near "hour"

Was this change from 7.1 to 7.2 intentional?  If so, how should this be 
coded in 7.2?

thanks,
--Barry


Thu, 15 Nov 2001 17:51:39 -0800
Barry Lind <barry@xythos.com>
bug or change in functionality in 7.2?
Well, the way I've always constructed these queries is:

select period_start + interval '1 hour' from periods;

Try that.  In fact, I believe the above is the correct SQL standard syntax?

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Barry Lind
> Sent: Friday, 16 November 2001 9:52 AM
> To: pgsql-general@postgresql.org
> Cc: PostgreSQL-development
> Subject: [HACKERS] bug or change in functionality in 7.2?
> 
> 
> I have in my code a SQL statement that does the following:
> 
> select period_start + interval('1 hour') from periods;
> 
> This worked in 7.1, but in 7.2 I am getting the following error:
> 
> ERROR:  parser: parse error at or near "'"
> 
> If I remove the quotes then I get the following error:
> 
> ERROR:  parser: parse error at or near "hour"
> 
> Was this change from 7.1 to 7.2 intentional?  If so, how should this be 
> coded in 7.2?
> 
> thanks,
> --Barry
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 


Fri, 16 Nov 2001 10:58:06 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: bug or change in functionality in 7.2?
Barry Lind <barry@xythos.com> writes:
> select period_start + interval('1 hour') from periods;
> This worked in 7.1, but in 7.2 I am getting the following error:
> ERROR:  parser: parse error at or near "'"

"interval" is a more reserved word than it used to be ("timestamp"
is too).  This is because interval(n) is now a type name, not a
function name, because we now support SQL92's notion of precision
specs for intervals and timestamps.  That means using "interval"
as an unquoted function name doesn't work anymore.

I concur with Christopher's recommendation: use the syntax
	interval '1 hour'
Other possibilities are
	cast('1 hour' as interval)
	"interval"('1 hour')
	'1 hour'::interval
The last two are Postgres-isms, the first two are SQL92 standard
notations that we'll try not to break in future.

			regards, tom lane

Fri, 16 Nov 2001 00:26:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] bug or change in functionality in 7.2? 
This needs to be highlighted in the release notes/history/migration
docs, whatever. both interval() and timestamp(), since that was a
(wrong) way to do casts, in the past.

Ross

On Fri, Nov 16, 2001 at 12:26:40AM -0500, Tom Lane wrote:
> Barry Lind <barry@xythos.com> writes:
> > select period_start + interval('1 hour') from periods;
> > This worked in 7.1, but in 7.2 I am getting the following error:
> > ERROR:  parser: parse error at or near "'"
> 
> "interval" is a more reserved word than it used to be ("timestamp"
> is too).  This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps.  That means using "interval"
> as an unquoted function name doesn't work anymore.
> 
> I concur with Christopher's recommendation: use the syntax
> 	interval '1 hour'
> Other possibilities are
> 	cast('1 hour' as interval)
> 	"interval"('1 hour')
> 	'1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

Fri, 16 Nov 2001 09:24:24 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: bug or change in functionality in 7.2?
Thanks for the quick help.  I have changed my code accordingly.

--Barry

Tom Lane wrote:

> Barry Lind <barry@xythos.com> writes:
> 
>>select period_start + interval('1 hour') from periods;
>>This worked in 7.1, but in 7.2 I am getting the following error:
>>ERROR:  parser: parse error at or near "'"
>>
> 
> "interval" is a more reserved word than it used to be ("timestamp"
> is too).  This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps.  That means using "interval"
> as an unquoted function name doesn't work anymore.
> 
> I concur with Christopher's recommendation: use the syntax
> 	interval '1 hour'
> Other possibilities are
> 	cast('1 hour' as interval)
> 	"interval"('1 hour')
> 	'1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.
> 
> 			regards, tom lane
> 
> 



Fri, 16 Nov 2001 09:29:00 -0800
Barry Lind <barry@xythos.com>
Re: [HACKERS] bug or change in functionality in 7.2?
On Fri, 16 Nov 2001 00:26:40 EST, Tom Lane wrote:
> Barry Lind <barry@xythos.com> writes:
> > select period_start + interval('1 hour') from periods;
> > This worked in 7.1, but in 7.2 I am getting the following error:
> > ERROR:  parser: parse error at or near "'"
> 
> "interval" is a more reserved word than it used to be ("timestamp"
> is too).  This is because interval(n) is now a type name, not a
> function name, because we now support SQL92's notion of precision
> specs for intervals and timestamps.  That means using "interval"
> as an unquoted function name doesn't work anymore.
> 
> I concur with Christopher's recommendation: use the syntax
> 	interval '1 hour'
> Other possibilities are
> 	cast('1 hour' as interval)
> 	"interval"('1 hour')
> 	'1 hour'::interval
> The last two are Postgres-isms, the first two are SQL92 standard
> notations that we'll try not to break in future.

  In my readings on the standard, the first one is _not_ SQL92
standard notation.  Indeed, I may be incorrect since I do not have an
actual copy of the SQL92 standard.  I am basing my statements on Date/
Darwin's "A guide to the SQL Standard", fourth edition.  In that tome,
they state:

----- cut -----
day-time:

  Written as the key word INTERVAL, followed by a (day-time) interval
string consisting of an opening single quote, an optional sign, a
continuous nonempty subsequence of dd, hh, mm, and ss[.[nnnnnn]] (with
a space separator between dd and the rest, if dd is specified, and
colon separators elsewhere), and a closing single quote, followed by
the appropriate "start [TO end]" specification.

  Examples:

  INTERVAL '1' 	MINUTE
  INTERVAL '2 12' DAY TO HOUR
  INTERVAL '2:12:35' HOUR TO SECOND
  INTERVAL '-4.50' SECOND

----- cut -----

  In my experiences with other databases, the notations indicated in
the Date/Darwin book do indeed work whereas the PostgreSQL notation
(with the closing single quote following the start to end
specification) do not work.

Thanks,
F Harvell

-- 
Mr. F Harvell                          Phone: +1.407.673.2529
FTS International Data Systems, Inc.    Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302                Fax: +1.407.673.4472
Winter Park, FL 32792                 mailto:fharvell@fts.net



Mon, 19 Nov 2001 11:16:49 -0500
F Harvell <fharvell@fts.net>
Re: [HACKERS] bug or change in functionality in 7.2? 
F Harvell <fharvell@fts.net> writes:
>   In my experiences with other databases, the notations indicated in
> the Date/Darwin book do indeed work whereas the PostgreSQL notation
> (with the closing single quote following the start to end
> specification) do not work.

In current sources:

regression=# select INTERVAL '2:12:35' HOUR TO SECOND;
 interval
----------
 02:12:35
(1 row)

regression=# select INTERVAL '2:12:35 HOUR TO SECOND';
ERROR:  Bad interval external representation '2:12:35 HOUR TO SECOND'
regression=#

Looks like Lockhart agrees with you ;-)

			regards, tom lane

Mon, 19 Nov 2001 11:24:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] bug or change in functionality in 7.2? 
On Mon, 19 Nov 2001 11:24:08 EST, Tom Lane wrote:
> F Harvell <fharvell@fts.net> writes:
> >   In my experiences with other databases, the notations indicated in
> > the Date/Darwin book do indeed work whereas the PostgreSQL notation
> > (with the closing single quote following the start to end
> > specification) do not work.
> 
> In current sources:
> 
> regression=# select INTERVAL '2:12:35' HOUR TO SECOND;
>  interval
> ----------
>  02:12:35
> (1 row)
> 
> regression=# select INTERVAL '2:12:35 HOUR TO SECOND';
> ERROR:  Bad interval external representation '2:12:35 HOUR TO SECOND'
> regression=#
> 
> Looks like Lockhart agrees with you ;-)
> 

If the above is true (i.e., errors on the second interval literal), it
should probably be mentioned in the release notes (HISTORY file?).
While I eagerly anticipate the change and agree with it, it will break
a lot of my current code.  I think this is (potentially) correct,
however, it should be told to people who are using interval literals
and anticipating to make the upgrade to 7.2.

-- 
Mr. F Harvell                          Phone: +1.407.673.2529
FTS International Data Systems, Inc.    Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302                Fax: +1.407.673.4472
Winter Park, FL 32792                 mailto:fharvell@fts.net



Mon, 19 Nov 2001 13:00:11 -0500
F Harvell <fharvell@fts.net>
Re: [HACKERS] bug or change in functionality in 7.2? 
> If the above is true (i.e., errors on the second interval literal), it
> should probably be mentioned in the release notes (HISTORY file?).
> While I eagerly anticipate the change and agree with it, it will break
> a lot of my current code.  I think this is (potentially) correct,
> however, it should be told to people who are using interval literals
> and anticipating to make the upgrade to 7.2.

Can I have some text for HISTORY?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 19 Nov 2001 16:06:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] bug or change in functionality in 7.2?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> If the above is true (i.e., errors on the second interval literal), it
>> should probably be mentioned in the release notes (HISTORY file?).

> Can I have some text for HISTORY?

Thomas would be the authority, but AFAIK this is new stuff; it doesn't
break anything that worked before.

			regards, tom lane

Mon, 19 Nov 2001 16:39:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] bug or change in functionality in 7.2? 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> If the above is true (i.e., errors on the second interval literal), it
> >> should probably be mentioned in the release notes (HISTORY file?).
> 
> > Can I have some text for HISTORY?
> 
> Thomas would be the authority, but AFAIK this is new stuff; it doesn't
> break anything that worked before.

Oh, OK.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 19 Nov 2001 16:41:11 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] bug or change in functionality in 7.2?
On Mon, 19 Nov 2001 16:41:11 EST, Bruce Momjian wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >> If the above is true (i.e., errors on the second interval literal), it
> > >> should probably be mentioned in the release notes (HISTORY file?).
> > 
> > > Can I have some text for HISTORY?
> > 
> > Thomas would be the authority, but AFAIK this is new stuff; it doesn't
> > break anything that worked before.
> 
> Oh, OK.

  Well, since I started this, I figured that I had best verify if
there is an issue.  There appears to be _no_ issue.  The exiting (7.1)
functionality still works in 7.2.  Sorry for the confusion.

  It might be reasonable, though, to mention in the types or
enhancements section that the SQL92 interval literal syntax is now
supported.  (It's implied but not spelled out as "Add INTERVAL() YEAR
TO MONTH (etc) syntax (Thomas)".)

  BTW, many thanks to Thomas.  This is a compatibility that I really
appreciate.

-- 
Mr. F Harvell                          Phone: +1.407.673.2529
FTS International Data Systems, Inc.    Cell: +1.407.467.1919
7457 Aloma Ave, Suite 302                Fax: +1.407.673.4472
Winter Park, FL 32792                 mailto:fharvell@fts.net



Tue, 20 Nov 2001 11:40:23 -0500
F Harvell <fharvell@fts.net>
Re: [HACKERS] bug or change in functionality in 7.2? 