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

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

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

Thanks for the quick help. I have changed my code accordingly.

--Barry

Tom Lane wrote:

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

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

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

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

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

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

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