Interval & check clause
Interval & check clause
От:
"Bruno BAGUETTE" <pgsql-ml@baguette.net>
Дата:
Hello, It may be a stupid question, but I'm looking to set a check clause in order to be sure that a INTERVAL field is 00:00:00 or higher, but NOT negatives values. How can I set that ? Thanks in advance :-) ------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
Interval constant syntax, was Re: Interval & check clause
От:
"Karl O. Pinc" <kop@meme.com>
Дата:
On 2004.03.29 09:40 Bruno Wolff III wrote:
> On Mon, Mar 29, 2004 at 14:30:14 +0200,
> Bruno BAGUETTE wrote:
> > Hello,
> >
> > It may be a stupid question, but I'm looking to set a check clause
> in order
> > to be sure that a INTERVAL field is 00:00:00 or higher, but NOT
> negatives
> > values.
> >
> > How can I set that ?
>
> check (col_name >= '0 day'::interval)
Is there any way to write a constant interval without a cast?
It sure seems awkward to always have to cast. (AT least I always
seem to have to cast after my little bit of experiementing.)
(BTW, check (col_name >= CAST ('0 days' AS interval))
is more portable.)
Karl
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Re: Interval constant syntax, was Re: Interval & check
От:
Stephan Szabo <sszabo@megazone.bigpanda.com>
Дата:
On Mon, 29 Mar 2004, Karl O. Pinc wrote:
>
> On 2004.03.29 14:44 Bruno Wolff III wrote:
> >
> > In postgres you shouldn't have to explicitly cast the constant to an
> > interval as long as there isn't one than one >= operator that could
> > be applied (depending on the eventaul type of the constant). I would
> > really be surprized if this were to happen for >= and an interval
> > operand
> > on one side or the other.
> >
> > It won't work with two unknown constants, if that was what you tested.
> > Try just casting on one side.
>
> This is my plpgsql code
>
> PERFORM MATUREDATES.sname FROM MATUREDATES
> WHERE NEW.sname = MATUREDATES.sname
> AND ( NEW.birth > MATUREDATES.Matured - ''3 years''
> OR NEW.birth > MATUREDATES.Matured - ''7 years'');
> IF FOUND THEN
>
> And this was my scratch psql test:
>
> => select CAST('1/1/2004' AS date) - '3 years';
> ERROR: Bad date external representation '3 years'
I think that's because (date - date) is the prefered interpretation.
The best way to specify an interval literal is probably
INTERVAL '3 years'
which is close to the SQL specification of an interval literal.
Re: Interval constant syntax, was Re: Interval & check clause
От:
"Karl O. Pinc" <kop@meme.com>
Дата:
On 2004.03.29 14:44 Bruno Wolff III wrote:
>
> In postgres you shouldn't have to explicitly cast the constant to an
> interval as long as there isn't one than one >= operator that could
> be applied (depending on the eventaul type of the constant). I would
> really be surprized if this were to happen for >= and an interval
> operand
> on one side or the other.
>
> It won't work with two unknown constants, if that was what you tested.
> Try just casting on one side.
This is my plpgsql code
PERFORM MATUREDATES.sname FROM MATUREDATES
WHERE NEW.sname = MATUREDATES.sname
AND ( NEW.birth > MATUREDATES.Matured - ''3 years''
OR NEW.birth > MATUREDATES.Matured - ''7 years'');
IF FOUND THEN
And this was my scratch psql test:
=> select CAST('1/1/2004' AS date) - '3 years';
ERROR: Bad date external representation '3 years'
I am using 7.3 so maybe this has been fixed. Or it's just the
operator, some require casts and some don't and you just have to find
out the hard way. :(
Karl
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Re: Interval & check clause
От:
Bruno Wolff III <bruno@wolff.to>
Дата:
On Mon, Mar 29, 2004 at 14:30:14 +0200, Bruno BAGUETTE wrote: > Hello, > > It may be a stupid question, but I'm looking to set a check clause in order > to be sure that a INTERVAL field is 00:00:00 or higher, but NOT negatives > values. > > How can I set that ? check (col_name >= '0 day'::interval)
Re: Interval constant syntax, was Re: Interval & check clause
От:
Bruno Wolff III <bruno@wolff.to>
Дата:
On Mon, Mar 29, 2004 at 13:58:51 -0600,
"Karl O. Pinc" wrote:
>
> Is there any way to write a constant interval without a cast?
> It sure seems awkward to always have to cast. (AT least I always
> seem to have to cast after my little bit of experiementing.)
>
> (BTW, check (col_name >= CAST ('0 days' AS interval))
> is more portable.)
In postgres you shouldn't have to explicitly cast the constant to an
interval as long as there isn't one than one >= operator that could
be applied (depending on the eventaul type of the constant). I would
really be surprized if this were to happen for >= and an interval operand
on one side or the other.
It won't work with two unknown constants, if that was what you tested.
Try just casting on one side.
P.S.
I think :: is easier to read than a CAST function call, so that is what
I am in the habit of using.
Re: Interval constant syntax, was Re: Interval & check clause
От:
Bruno Wolff III <bruno@wolff.to>
Дата:
On Mon, Mar 29, 2004 at 15:28:59 -0600,
"Karl O. Pinc" wrote:
>
> And this was my scratch psql test:
>
> => select CAST('1/1/2004' AS date) - '3 years';
> ERROR: Bad date external representation '3 years'
>
> I am using 7.3 so maybe this has been fixed. Or it's just the
> operator, some require casts and some don't and you just have to find
> out the hard way. :(
This is different in that you are using - instead of >= . Date - Date
will get picked because that is the only - operator with a left operand
of type date. You are trying to use timestamp - interval (with date
getting promoted to timestamp).
Re: Interval constant syntax, was Re: Interval & check clause
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bruno Wolff III writes: > This is different in that you are using - instead of >= . Date - Date > will get picked because that is the only - operator with a left operand > of type date. Even if it weren't the only one, there is a preferential case involved: given "known_type operator unknown_literal", the parser will preferentially assume that the unknown_literal is the same type as the other operand, if there is an available operator that takes that type on both sides. For details see http://www.postgresql.org/docs/7.4/static/typeconv-oper.html (rule 2a is my point here). Note that in *no* case will the contents of the string literal have any a-priori effect on the parser's decision about what the literal's type is. I believe this is a good policy in general --- doing otherwise would render the behavior way too unpredictable, since often the contents of the literal are not under the control of the SQL query author. But this does mean that just writing '2 days' is not going to be enough to make the system think it is an interval constant. There must be some cue to the type outside the quotes, whether an explicit cast or an implicit match to another operand. regards, tom lane
