Thread: 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
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
>
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
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
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
>
>
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