Обсуждение: 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
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
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