partition text/varchar check problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,
I am trying to make partitions:

CREATE SEQUENCE data_seq;
CREATE TABLE data (
    identyf     bigint,
    name        varchar,
    added       timestamp default now()
);

/*********************************************/
CREATE TABLE data_a (CHECK (name LIKE varchar 'a%')
) INHERITS (data);
--
CREATE TABLE data_b (CHECK (name LIKE varchar 'b%')
) INHERITS (data);

/*********************************************/
CREATE INDEX data_a_idx ON data_a(name);
CREATE INDEX data_b_idx ON data_b(name);

/*********************************************/
CREATE RULE data_insert_a AS ON INSERT TO data WHERE (name LIKE 'a%')
DO INSTEAD INSERT INTO data_a(identyf,name) VALUES
(nextval('data_seq'),NEW.name);
--
CREATE RULE data_insert_b AS ON INSERT TO data WHERE (name LIKE 'b%')
DO INSTEAD INSERT INTO data_b(identyf,name) VALUES
(nextval('data_seq'),NEW.name);


I put some data and vacuum:

/*********************************************/
INSERT INTO data(name) VALUES ('aaa');
INSERT INTO data(name) VALUES ('aab');
INSERT INTO data(name) VALUES ('baa');
INSERT INTO data(name) VALUES ('bab');

VACUUM ANALYZE data_a;
VACUUM ANALYZE data_b;

/*********************************************/
SET constraint_exclusion=off;
SET
EXPLAIN SELECT * FROM data WHERE name = 'aaa';
                               QUERY PLAN
------------------------------------------------------------------------
 Result  (cost=0.00..24.42 rows=7 width=48)
   ->  Append  (cost=0.00..24.42 rows=7 width=48)
         ->  Seq Scan on data  (cost=0.00..22.38 rows=5 width=48)
               Filter: ((name)::text = 'aaa'::text)
         ->  Seq Scan on data_a data  (cost=0.00..1.02 rows=1 width=23)
               Filter: ((name)::text = 'aaa'::text)
         ->  Seq Scan on data_b data  (cost=0.00..1.02 rows=1 width=23)
               Filter: ((name)::text = 'aaa'::text)
(8 rows)


/*********************************************/
SET constraint_exclusion=on;
SET

SHOW constraint_exclusion;
 constraint_exclusion
----------------------
 on
(1 row)

EXPLAIN SELECT * FROM data WHERE name = 'aaa';
                               QUERY PLAN
------------------------------------------------------------------------
 Result  (cost=0.00..24.42 rows=7 width=48)
   ->  Append  (cost=0.00..24.42 rows=7 width=48)
         ->  Seq Scan on data  (cost=0.00..22.38 rows=5 width=48)
               Filter: ((name)::text = 'aaa'::text)
         ->  Seq Scan on data_a data  (cost=0.00..1.02 rows=1 width=23)
               Filter: ((name)::text = 'aaa'::text)
         ->  Seq Scan on data_b data  (cost=0.00..1.02 rows=1 width=23)
               Filter: ((name)::text = 'aaa'::text)
(8 rows)


I have tried with name as text in data table and in CHECK. Where do I
have an error? Is it possible to make partitions with strings?

Thank you for any clues.

Best regards,
jamcito

----------------------------------------------------------------------
smieszne, muzyka, pilka, sexy, kibice, kino, ciekawe, extreme, kabaret
http://link.interia.pl/f19d4 - najlepsze filmy w intermecie



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux