agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
2+ messages / 1 participants
[nested] [flat]
* PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
@ 2013-10-16 12:50 Craig R. Skinner <skinner@britvault.co.uk>
0 siblings, 1 reply; 2+ messages in thread
From: skinner@britvault.co.uk @ 2013-10-16 12:50 UTC (permalink / raw)
To: pgsql-sql
I'd like to limit a 9.2 tstzrange to valid dates at both ends.
No NULLs nor 'infinity'.
Various revisions of this SQL isn't constraining '-/+infinity' input:
CREATE TABLE bill
(
id serial NOT NULL,
created_at timestamp with time zone NOT NULL DEFAULT now(),
period tstzrange NOT NULL,
-- other columns
CONSTRAINT bill_pk PRIMARY KEY (id),
CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
CONSTRAINT bill_period_lower_not_infinity_ck CHECK (lower_inf(period) IS FALSE),
CONSTRAINT bill_period_upper_not_infinity_ck CHECK (upper_inf(period) IS FALSE)
);
INSERT INTO bill (period) VALUES (tstzrange (NULL, NULL));
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', '2015-02-22');
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', now());
INSERT INTO bill (period) VALUES (tstzrange ('infinity', now());
INSERT INTO bill (period) VALUES (tstzrange ('-infinity', now());
INSERT INTO bill (period) VALUES (tstzrange (now(), 'infinity');
psql (9.2.3)
Type "help" for help.
test_1=> SELECT id, period FROM bill;
id | period
----+------------------------------------------------------------
1 | ["2000-05-10 00:00:00+01","2015-02-22 00:00:00+00")
2 | ["2000-05-10 00:00:00+01","2013-10-16 13:20:01.793803+01")
4 | [-infinity,"2013-10-16 13:20:18.993038+01")
5 | ["2013-10-16 13:21:14.208279+01",infinity)
(4 rows)
Changing the *_inf(period) IS FALSE checks to TRUE rejects valid dates.
Range functions in the manual: http://www.postgresql.org/docs/9.2/static/functions-range.html
Thoughts?
--
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
@ 2013-10-16 14:41 Craig R. Skinner <skinner@britvault.co.uk>
parent: Craig R. Skinner <skinner@britvault.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: skinner@britvault.co.uk @ 2013-10-16 14:41 UTC (permalink / raw)
To: pgsql-sql
On 2013-10-16 Wed 13:50 PM |, Craig R. Skinner wrote:
> I'd like to limit a 9.2 tstzrange to valid dates at both ends.
> No NULLs nor 'infinity'.
>
> CREATE TABLE bill
> (
> id serial NOT NULL,
> created_at timestamp with time zone NOT NULL DEFAULT now(),
> period tstzrange NOT NULL,
> -- other columns
> CONSTRAINT bill_pk PRIMARY KEY (id),
This works:
CONSTRAINT bill_period_not_null_nor_infinity_ck CHECK
(
lower(period) IS NOT NULL AND
upper(period) IS NOT NULL AND
(lower(period) > '-infinity') AND
(upper(period) < 'infinity')
)
Cheers,
--
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-10-16 14:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-16 12:50 PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK Craig R. Skinner <skinner@britvault.co.uk>
2013-10-16 14:41 ` Craig R. Skinner <skinner@britvault.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox