Thread: Interval & check clause
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
On Mon, Mar 29, 2004 at 14:30:14 +0200, Bruno BAGUETTE <pgsql-ml@baguette.net> 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)
On 2004.03.29 09:40 Bruno Wolff III wrote:
> On Mon, Mar 29, 2004 at 14:30:14 +0200,
> Bruno BAGUETTE <pgsql-ml@baguette.net> 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 <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
On Mon, Mar 29, 2004 at 13:58:51 -0600,
"Karl O. Pinc" <kop@meme.com> 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.
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 <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
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.
On Mon, Mar 29, 2004 at 15:28:59 -0600,
"Karl O. Pinc" <kop@meme.com> 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).
Bruno Wolff III <bruno@wolff.to> 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