Problem while Inserting data in Postgres Partitioned table
-
CREATE TABLE nlma.acc_monthly_closing_details ( str_voucher_code character varying(70) NOT NULL, int_transaction_no integer NOT NULL, str_voucher_no character varying(50) NOT NULL, dt_voucher_date timestamp without time zone NOT NULL, int_payable_id integer NOT NULL, num_amount double precision NOT NULL DEFAULT 0, char_cash_chq_auth character varying(1) NOT NULL, char_credit_debit character varying(1) NOT NULL, CONSTRAINT pk_monthly_closing_details_vcode_tranno PRIMARY KEY (str_voucher_code, int_transaction_no) ) WITH ( OIDS=FALSE ); CREATE OR REPLACE RULE insert_into_acc_monthly_closing_details_2010_01 AS ON INSERT TO nlma.acc_monthly_closing_details WHERE new.dt_voucher_date >= '2010-01-01'::date AND new.dt_voucher_date <= '2010-01-31'::date DO INSTEAD INSERT INTO nlma.acc_monthly_closing_details_2010_01 (str_voucher_code, int_transaction_no, str_voucher_no, dt_voucher_date, int_payable_id, num_amount, char_cash_chq_auth, str_chq_no, dt_chq_date, char_credit_debit) VALUES (new.str_voucher_code, new.int_transaction_no, new.str_voucher_no, new.dt_voucher_date, new.int_payable_id, new.num_amount, new.char_cash_chq_auth, new.str_chq_no, new.dt_chq_date, new.char_credit_debit); CREATE TABLE nlma.acc_monthly_closing_details_2010_01 ( CONSTRAINT acc_monthly_closing_details_2010_01_dt_voucher_date_check CHECK (dt_voucher_date >= '2010-01-01'::date AND dt_voucher_date <= '2010-01-31'::date) ) INHERITS (nlma.acc_monthly_closing_details) WITH ( OIDS=FALSE ); CREATE INDEX acc_monthly_closing_details_2010_01_dt_voucher_date ON nlma.acc_monthly_closing_details_2010_01 USING btree (dt_voucher_date); the same way i have another 11 child tables, when i want to insert data using INSERT INTO nlma.acc_monthly_closing_details SELECT a.str_voucher_code, b.int_transaction_no, a.str_voucher_no,a.dt_voucher_date, a.int_payable_id,b.num_amount,a.char_cash_chq_auth,a.str_chq_no, a.dt_chq_date, b.char_credit_debit, FROM nlma.acc_voucher_master a, nlma.acc_voucher_details b WHERE a.int_voucher_month=int_month and a.int_voucher_year=int_year and a.str_voucher_code=b.str_voucher_code; It will Insert in Both Master as well as one child table. IF any mistake in above code.. Please mention...
-
CREATE TABLE nlma.acc_monthly_closing_details ( str_voucher_code character varying(70) NOT NULL, int_transaction_no integer NOT NULL, str_voucher_no character varying(50) NOT NULL, dt_voucher_date timestamp without time zone NOT NULL, int_payable_id integer NOT NULL, num_amount double precision NOT NULL DEFAULT 0, char_cash_chq_auth character varying(1) NOT NULL, char_credit_debit character varying(1) NOT NULL, CONSTRAINT pk_monthly_closing_details_vcode_tranno PRIMARY KEY (str_voucher_code, int_transaction_no) ) WITH ( OIDS=FALSE ); CREATE OR REPLACE RULE insert_into_acc_monthly_closing_details_2010_01 AS ON INSERT TO nlma.acc_monthly_closing_details WHERE new.dt_voucher_date >= '2010-01-01'::date AND new.dt_voucher_date <= '2010-01-31'::date DO INSTEAD INSERT INTO nlma.acc_monthly_closing_details_2010_01 (str_voucher_code, int_transaction_no, str_voucher_no, dt_voucher_date, int_payable_id, num_amount, char_cash_chq_auth, str_chq_no, dt_chq_date, char_credit_debit) VALUES (new.str_voucher_code, new.int_transaction_no, new.str_voucher_no, new.dt_voucher_date, new.int_payable_id, new.num_amount, new.char_cash_chq_auth, new.str_chq_no, new.dt_chq_date, new.char_credit_debit); CREATE TABLE nlma.acc_monthly_closing_details_2010_01 ( CONSTRAINT acc_monthly_closing_details_2010_01_dt_voucher_date_check CHECK (dt_voucher_date >= '2010-01-01'::date AND dt_voucher_date <= '2010-01-31'::date) ) INHERITS (nlma.acc_monthly_closing_details) WITH ( OIDS=FALSE ); CREATE INDEX acc_monthly_closing_details_2010_01_dt_voucher_date ON nlma.acc_monthly_closing_details_2010_01 USING btree (dt_voucher_date); the same way i have another 11 child tables, when i want to insert data using INSERT INTO nlma.acc_monthly_closing_details SELECT a.str_voucher_code, b.int_transaction_no, a.str_voucher_no,a.dt_voucher_date, a.int_payable_id,b.num_amount,a.char_cash_chq_auth,a.str_chq_no, a.dt_chq_date, b.char_credit_debit, FROM nlma.acc_voucher_master a, nlma.acc_voucher_details b WHERE a.int_voucher_month=int_month and a.int_voucher_year=int_year and a.str_voucher_code=b.str_voucher_code; It will Insert in Both Master as well as one child table. IF any mistake in above code.. Please mention...
Not sure the purpose of the child tables. You also need a rule for when an update occurs on the master table since you are replicating data in two tables. Why not only have the master table with single inserts and create views on the master table that have the hard coded date ranges built into the views? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
Not sure the purpose of the child tables. You also need a rule for when an update occurs on the master table since you are replicating data in two tables. Why not only have the master table with single inserts and create views on the master table that have the hard coded date ranges built into the views? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Thank you for your quick reply Chris. I would be having around 5 million records per month in my table. That is why I thought of partitioning the table on the basis of months to generate month-wise reports. May be I got it wrong, but my understanding was that the data would be inserted only in the child tables and not the master table. Due to the amount of data, the query being fired takes 4-5 mins to return any results (basically it is using the WITH RECURSIVE clause). I used indexes and also tried optimizing the query but it hardly made any difference. I might have to write the query in a different manner, but the problem is still with the amount of data in the table. Kindly suggest on how to resolve this issue. Thank you in advance.