Bruce Momjian wrote:
This is going to be a backward compatibility problem
You say that as if it's a bad thing.
In a previous thread, I think Bruce and Tom both commented on
ripping out some of the weird undocumented interval behavior:
http://archives.postgresql.org/pgsql-patches/2003-09/msg00134.
Bruce Momjian writes:
> Agreed. What is really weird is that the time is always displayed for a
> zero value:
> test=> select interval '0 years' year;
>interval
> --
>00:00:00
> (1 row)
> but a non-zero shows the proper units:
> test=> se
Tom Lane wrote:
> Roy Badami <[EMAIL PROTECTED]> writes:
> > test=> select interval '1 hour 1 minute';
> > interval
> > --
> > 01:01:00
> > (1 row)
>
> > Hmm, I don't think I really like having a seconds field in the output,
> > given that the column is by definition
Roy Badami <[EMAIL PROTECTED]> writes:
> test=> select interval '1 hour 1 minute';
>interval
> --
>01:01:00
> (1 row)
> Hmm, I don't think I really like having a seconds field in the output,
> given that the column is by definition only storing data to a
>
test=> select interval '1 hour 1 minute';
interval
--
01:01:00
(1 row)
Hmm, I don't think I really like having a seconds field in the output,
given that the column is by definition only storing data to a
precision of a minute.
Roy Badami wrote:
> Roy> The 'constraint' (interval type descriptor or whatever it's
> Roy> really called) is mandatory in standard SQL, I think, so
> Roy> there's no ambiguity anyway, unless anyone is using this
> Roy> undocumented syntax at the moment...
>
> Incidentally, this wa
Tom Lane wrote:
> Roy Badami <[EMAIL PROTECTED]> writes:
> > Tom> We should do that *only* when an ISO (not ANSI)
> > Just to clarify, is that a distinction or just a clarification? (ie
> > are ANSI and ISO SQL different?)
>
> There is no "ANSI SQL". Only ISO SQL. ANSI never issued their ow
Tom> As you point out, the ISO spec doesn't actually say that that
Tom> string literal has to be the external textual representation
Tom> ... but it's surely pretty bizarre that it's not a good
Tom> candidate to be the textual representation.
Yes, I'm convicned. Particularly after
Roy Badami <[EMAIL PROTECTED]> writes:
> Tom> Yes. The ISO design for the datatype is pretty brain-dead if
> Tom> you ask me --- the basic meaning of a data literal shouldn't
> Tom> be so dependent on context.
> Arguably it's not, because the interval qualifier is an intrinsic (and
>
Roy Badami <[EMAIL PROTECTED]> writes:
> That convinces me that you have to figure out what to do with
> CAST ('1 year 1 month' AS INTERVAL MONTH)
> It _still_ doesn't completely convince me that you need to (or even
> want to) support hybrid interval syntax.
> Though given you have to support
Tom> Yes. The ISO design for the datatype is pretty brain-dead if
Tom> you ask me --- the basic meaning of a data literal shouldn't
Tom> be so dependent on context.
Arguably it's not, because the interval qualifier is an intrinsic (and
mandatory) part of the literal syntax, so the fu
Tom> It's not different from
Tom> INSERT INTO foo VALUES('1 year 1 month');
Tom> Nothing nonstandard about that that I can see.
Oh, does ISO allow a cast from a string to an interval? Yes, I think
it probably does. And with the interpretation of the string is
dependent on the ISO
Roy Badami <[EMAIL PROTECTED]> writes:
> Tom> In particular we have to consider the behavior of the input
> Tom> and output routines for cases like COPY.
> Hmm, but COPY is non-standard, so I'd be happy that it insisted on
> postgres interval syntax.
It's not different from
INSERT INTO f
Tom Lane wrote:
> There is no "ANSI SQL". Only ISO SQL. ANSI never issued their own
> spec to my knowledge ... unlike, say, C, where I believe ANSI C was
> in fact issued by ANSI.
There is an ANSI SQL standard ANSI X3.135. At least my copy of SQL 1999
carries a combined ANSI/ISO label. Nevert
Tom> In the context of interval literals it's probably
Tom> unnecessary, but that's not the only thing to worry about.
Tom> In particular we have to consider the behavior of the input
Tom> and output routines for cases like COPY. I think it would be
Tom> really bad to reject '
Roy Badami <[EMAIL PROTECTED]> writes:
> Tom> We should do that *only* when an ISO (not ANSI)
> Just to clarify, is that a distinction or just a clarification? (ie
> are ANSI and ISO SQL different?)
There is no "ANSI SQL". Only ISO SQL. ANSI never issued their own
spec to my knowledge ... u
Roy Badami <[EMAIL PROTECTED]> writes:
> Roy> The 'constraint' (interval type descriptor or whatever it's
> Roy> really called) is mandatory in standard SQL,
True. ( is what SQL99 calls it.)
> I have no objection to allowing things like
>'1 hour 10 minutes' DAY TO SECOND
> but I'm ju
Tom> We should do that *only* when an ISO (not ANSI)
Just to clarify, is that a distinction or just a clarification? (ie
are ANSI and ISO SQL different?)
-roy
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
Roy> I have no objection to allowing things like
Roy>'1 hour 10 minutes' DAY TO SECOND
Roy> but I'm just wondering whether the hybrid syntax is an
Roy> unnecessary complication.
And furthermore, if you really want to allow constrained postgres
syntax interval literals (and I
Roy> The 'constraint' (interval type descriptor or whatever it's
Roy> really called) is mandatory in standard SQL, I think, so
Roy> there's no ambiguity anyway, unless anyone is using this
Roy> undocumented syntax at the moment...
Incidentally, this was the ratinale behind my earli
Tom> That would eliminate the backward-compatibility problem
Tom> pretty well (since the constraints aren't documented and
Tom> hence aren't being used now), while not posing a big problem
Tom> for ISO cases (since if there's no constraint there are no
Tom> ambiguous cases, I be
Bruce Momjian writes:
> o Interpret syntax that isn't uniquely ANSI or PG, like '1:30' or
> '1' as ANSI syntax, e.g. interpret '1:30' MINUTE TO SECOND as
> '1 minute 30 seconds'
> This is going to be a backward compatibility problem, but to support
> ANSI syntax we are
Roy Badami wrote:
>
> Bruce> This is going to be a backward compatibility problem, but
> Bruce> to support ANSI syntax we are going to need to do this.
>
> Given the existence of the INTERVAL '1' MONTH etc syntax is completely
> undocumented, I doubt it's a major problem.
>
> (Actually,
Bruce> This is going to be a backward compatibility problem, but
Bruce> to support ANSI syntax we are going to need to do this.
Given the existence of the INTERVAL '1' MONTH etc syntax is completely
undocumented, I doubt it's a major problem.
(Actually, INTERVAL '1' and INTERVAL '1:30' a
Roy Badami wrote:
>
> Bruce> The reason I added it is that usually people think of the
> Bruce> PG syntax as '1 hour 30 seconds'. The '1:30' is a kind of
> Bruce> subtle because both PG and ANSI support that syntax and we
> Bruce> need to handle that. The tricky part is that we c
Bruce> The reason I added it is that usually people think of the
Bruce> PG syntax as '1 hour 30 seconds'. The '1:30' is a kind of
Bruce> subtle because both PG and ANSI support that syntax and we
Bruce> need to handle that. The tricky part is that we can't say
Bruce> by looki
Roy Badami wrote:
>
> Bruce>* Interpret INTERVAL '1:30' MINUTE TO SECOND as '1
> Bruce> minute 30 seconds'
>
> This seems redundant; it's just another example of the ANSI day-time
> interval syntax...
The reason I added it is that usually people think of the PG syntax as
'1 hour 30 s
Bruce> * Interpret INTERVAL '1:30' MINUTE TO SECOND as '1
Bruce> minute 30 seconds'
This seems redundant; it's just another example of the ANSI day-time
interval syntax...
-roy
---(end of broadcast)---
TIP 3: if posting/read
Added to TODO also:
* Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 minute 30 seconds'
---
Tom Lane wrote:
> Roy Badami <[EMAIL PROTECTED]> writes:
> > Similary the undocumented postgresism of interpreting
> >
Roy Badami <[EMAIL PROTECTED]> writes:
> ... but all of them have (in ANSI SQL) disitinct data types.
They are distinct types, or at least different typmods, in
Postgres as well.
regards, tom lane
---(end of broadcast)---
TI
Bruce Momjian writes:
> I guess my point is that we should allow:
> select interval '1' day '1' hour
> as SQL standard
Where do you get that that's in the SQL standard?
What is in the standard is
::=
INTERVAL [ ]
::=
{ | }
Bruce Momjian writes:
> I am wondering why we allow the 'interval' data type specification to be
> after the string.
Because that's what the standard demands. Please don't muddy the waters
by introducing yet more nonstandard syntax into the discussion.
regards, tom lane
Roy> It would be
Roy>INTERVAL '1 1' DAY TO HOUR
Actually, it would be any one of the following:
INTERVAL '1 1' DAY TO HOUR
INTERVAL '1 1:00' DAY TO MINUTE
INTERVAL '1 1:00:00' DAY TO SECOND
INTERVAL '25' HOUR
INTERVAL '25:00' HOUR TO MINUTE
INTERVAL '25:00:00' HOUR
Bruce> I guess my point is that we should allow:
Bruce> select interval '1' day '1' hour
Bruce> as SQL standard and equavalent to:
Ah, I think you're misunderstanding what the SQL standard interval
literal syntax looks like.
It would be
INTERVAL '1 1' DAY TO HOUR
Essential
Roy Badami wrote:
> Bruce>select interval day to second '1 day 1 hour'
>
> Bruce> However, we don't support that syntax, only the one with
> Bruce> the specification after.
>
> Is that valid ANSI SQL?
I guess my point is that we should allow:
select interval '1' day '1'
Bruce> test=> select timestamp with time zone '2004-01-01';
Also, FWIW, according to the postgres doc this is a postgresism. The
'with time zone' clause never occurs in an ANSI timestamp literal;
whether it is a timestamp or a timestamp with time zone depends on
whether a time zone spe
Bruce> somehow. Right now we use the clause after the string as
Bruce> the date type specification, and I see you saying that the
Bruce> data value specification has to after the string. Is that
Bruce> correct?
Well, that's what 'A guide to the SQL standard' gives as the syntax
f
Roy Badami wrote:
> Bruce>select interval day to second '1 day 1 hour'
>
> Bruce> However, we don't support that syntax, only the one with
> Bruce> the specification after.
>
> Is that valid ANSI SQL?
No idea. It just seemed like the data type specification and the data
value s
Roy Badami wrote:
> Tom> Feel like hacking the code?
>
> Hmm, in principle I might take a look some time; in reality it's
> unlikely I'll have time any time soon...
>
> There are some design issues involved, though. If you have the type
> modifier, do you isnist on SQL syntax in the string?
Bruce> select interval day to second '1 day 1 hour'
Bruce> However, we don't support that syntax, only the one with
Bruce> the specification after.
Is that valid ANSI SQL?
-roy
---(end of broadcast)---
TIP 9: the planner will
Roy Badami <[EMAIL PROTECTED]> writes:
> Similary the undocumented postgresism of interpreting
>INTERVAL '1:02'
> as 1 hour 2 minutes is consistent with the ANSI
> INTERVAL '1:02' HOUR TO MINUTE
> but not with the ANSI
>INTERVAL '1:02' MINUTE TO SECOND
> which of course mea
Similary the undocumented postgresism of interpreting
INTERVAL '1:02'
as 1 hour 2 minutes is consistent with the ANSI
INTERVAL '1:02' HOUR TO MINUTE
but not with the ANSI
INTERVAL '1:02' MINUTE TO SECOND
which of course means 1 minute 2 seconds.
The fact is that AN
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes:
>> ie do you accept interval '1 day 1 hour' day to second
Tom> I think we have to, and the reason is that this isn't
Tom> different under the hood from reading the external value '1
Tom> day 1 hour' and storing it into a column t
Roy Badami <[EMAIL PROTECTED]> writes:
> ie do you accept
>interval '1 day 1 hour' day to second
I think we have to, and the reason is that this isn't different under
the hood from reading the external value '1 day 1 hour' and storing
it into a column that has the DAY TO SECOND typmod. If we
Tom> Feel like hacking the code?
Hmm, in principle I might take a look some time; in reality it's
unlikely I'll have time any time soon...
There are some design issues involved, though. If you have the type
modifier, do you isnist on SQL syntax in the string?
ie do you accept
interval '
Roy Badami <[EMAIL PROTECTED]> writes:
> All the documented interval functionality works fine. The
> undocumented support for ANSI SQL interval data types and litereals
> doesn't :-/
I think the reason it's not documented is precisely that Tom never
finished it. It may not be very far away thoug
Bruce> So, we have a few major problems with intervals. Let me
Bruce> think a little and I will summarize.
FWIW, AFAICT the problems I reported in bug 1517 and 1518 all relate
to undocumented features of PostgreSQL.
All the documented interval functionality works fine. The
undocumented
Roy Badami wrote:
>
> Tom> No, it shouldn't; read the SQL spec. AFAICS the syntax
> Tom> select interval '1' month is perfectly spec-compliant. The
> Tom> variant select interval '1 month' is *not* per-spec, it is a
> Tom> Postgres-ism.
>
> That is my understanding, though I don
Tom> AFAICS the syntax
Tom> select interval '1' month
Tom> is perfectly spec-compliant.
Well, it's not _perfectly_ spec compliant, because AIUI SELECTs
without FROM clauses are a postgres-ism, too. But I'm just
nitpicking...
-roy
---(end of br
Tom> No, it shouldn't; read the SQL spec. AFAICS the syntax
Tom> select interval '1' month is perfectly spec-compliant. The
Tom> variant select interval '1 month' is *not* per-spec, it is a
Tom> Postgres-ism.
That is my understanding, though I don't have a copy of the spec (my
r
Bruce Momjian writes:
> Well, that certainly belongs in the 'bizarre' category. It should not
> accept that syntax. It should require the 'month' or 'minute' to be in
> single quotes.
No, it shouldn't; read the SQL spec. AFAICS the syntax
select interval '1' month
is perfectly spec-com
Roy Badami wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1517
> Logged by: Roy Badami
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.1
> Operating system: Solaris 9
> Description:SQL interval syntax is accepted by the parser, bu
The following bug has been logged online:
Bug reference: 1517
Logged by: Roy Badami
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: Solaris 9
Description:SQL interval syntax is accepted by the parser, but the
interpretation is bogus
Detail
53 matches
Mail list logo
