tests.sql

text/x-sql

Download

/* example 1 */
DROP TABLE IF EXISTS padre CASCADE;

create table padre(i serial primary key, d date); 
create table hija_2010 () inherits (padre);
create table hija_2011 () inherits (padre);
insert into hija_2010(d) values ('2011-08-15'::date);
insert into hija_2011(d) values ('2011-09-15'::date);
alter table hija_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
alter table hija_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date;

create table hija_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (padre);
insert into hija_2009(d) values ('2009-06-13');

explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date;
explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date;

alter table hija_2011 VALIDATE CONSTRAINT hija_2011_d_check;

explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date;


/* example 2 */
create domain mes as int;
create table t_mes (m mes);
insert into t_mes values(13);
alter domain mes add check (value between 1 and 12) not valid;

create table t_mes2 (m int);
insert into t_mes2 values(13);
alter table t_mes2 ALTER  m type mes;
ERROR:  value for domain mes violates check constraint "mes_check"