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

Comments

Post a Comment

Popular posts from this blog

ruby on rails - Devise Logout Error in RoR -

ruby on rails - Devise Logout Error in RoR - while using devise in ror, encountering unusual error when trying log out url different root url. displays me : actioncontroller::invalidauthenticitytoken in devise::sessionscontroller#destroy" for illustration if rooting path after log in '/structure', , seek log out '/general' error, while logging out '/structure' fine. any help highly appreciated. ruby-on-rails ruby devise
Read more

c# - Create a Notification Object (Email or Page) At Run Time -- Dependency Injection or Factory -

c# - Create a Notification Object (Email or Page) At Run Time -- Dependency Injection or Factory - i have code emails me notification when happens. trying add together page , also remove dependency on email. for reference; have name of notification want send stored string in database. @ runtime have list of people , string representation of want receive. i thought dependency injection ninject, kernel bindings got big , seemed hacking together. then thought simple factory, activator.createinstance(email/page) inotifcation, don't know how handle differing constructor parameters (unless utilize common, complex property, employee, has info need). so far have this: public interface inotification { void sendmessage(); } public class email: inotification { private readonly string _toemailaddress; private string body { { homecoming "email"; } } private static string smtphost { ...
Read more

model view controller - MVC Rails Planning -

model view controller - MVC Rails Planning - i'm starting rails , i'm building app stores films info, user can watch film , give grade on web application. i have coded cinema model, controller , views. now, have 2 questions: should need create model , controller grading? in case, should create or should wait , create first users model , controller? yes, on model , controller grades. that'd feasible way maintain track of who's graded what. it's create first, i'd start users, since grades depend on both films , users. ruby-on-rails model-view-controller
Read more