sql - postgresql partitioning master table duplicate entries -

sql - postgresql partitioning master table duplicate entries -

i ve created simple partitioning construction given below:

master table

create table parent_table ( id_n numeric(19,0) not null, name_v character varying(255), location_n numeric(19,0), constraint parent_table_pkey primary key (id_n ) )

child tables

create table child_table_location_1 ( -- inherited table parent_table: id_n numeric(19,0) not null, -- inherited table parent_table: name_v character varying(255), -- inherited table parent_table: location_n numeric(19,0), constraint child_table_location_1_pkey primary key (id_n ), constraint child_table_location_1_location_n_check check (location_n = 1::numeric) )inherits (parent_table) create table child_table_location_2 ( -- inherited table parent_table: id_n numeric(19,0) not null, -- inherited table parent_table: name_v character varying(255), -- inherited table parent_table: location_n numeric(19,0), constraint child_table_location_2_pkey primary key (id_n ), constraint child_table_location_2_location_n_check check (location_n = 2::numeric) )inherits (parent_table)

trigger

create or replace function partition_insertion_trigger() returns trigger $$ begin execute 'insert '|| quote_ident('child_table_location_'||new.location_n)||' select ($1).*' using new; homecoming new; end; $$ language plpgsql; create trigger insert_into_partition_trigger before insert on parent_table each row execute procedure partition_insertion_trigger();

i create trigger such based on location field, info gets populated in respective kid tables.

now ve below insert scripts:

insert parent_table(id_n, name_v, location_n) values (1, 'aaa', 1); insert parent_table(id_n, name_v, location_n) values (2, 'bbb', 2); insert parent_table(id_n, name_v, location_n) values (3, 'ccc', 1); insert parent_table(id_n, name_v, location_n) values (4, 'ddd', 2); insert parent_table(id_n, name_v, location_n) values (5, 'eee', 1); insert parent_table(id_n, name_v, location_n) values (6, 'fff', 2);

when select individual tables:

child table 1

id_n| name_v| location_n 1|"aaa"|1 3|"ccc"|1 5|"eee"|1

child table 2

id_n| name_v| location_n 2|"bbb"|2 4|"ddd"|2 6|"fff"|2

but when query on master table:

parent table

id_n|name_v|location_n 1|"aaa"|1 1|"aaa"|1 2|"bbb"|2 2|"bbb"|2 3|"ccc"|1 3|"ccc"|1 4|"ddd"|2 4|"ddd"|2 5|"eee"|1 5|"eee"|1 6|"fff"|2 6|"fff"|2

i duplicate entries parent_table after having primary key constraint on id field.

why happening , changes should create on design.

awaiting quick response.

thanks in advance.

returning null not clean way.

the elegant solution have trigger "instead of"

see: http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html

sql postgresql postgresql-9.1 database-partitioning