{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fmodern\fprq1\fcharset0 Courier New;}{\f1\fnil\fcharset0 Courier New;}} {\*\generator Msftedit 5.41.21.2500;}\viewkind4\uc1\pard\b\f0\fs20 --\par -- Run the SQL commands in partition.sql(found in base directory 'pgsql_init') to setup trigger functions \par -- for partitioning. This file is generated by master makefile. \par -- psql -p xxxx < partition.sql\par -- \par \par drop table a;\par CREATE TABLE a(\par emp_id int not null,\par designation text not null,\par location varchar(50) not null,\par jdate date not null,\par ctc float not null\par );\par \par INSERT INTO a VALUES (1567, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO a VALUES (1568, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO a VALUES (3501, 'CEO', 'Pune', '17 June, 2006', 30000);\par INSERT INTO a VALUES (3502, 'CEO', 'Pune', '17 June, 2006', 30000);\par INSERT INTO a VALUES (3503, 'CEO', 'Pune', '17 June, 2006', 30000);\par INSERT INTO a VALUES (1525, 'System Administrator', 'Chennai', '7 July, 2004', 35000);\par \par DROP TABLE emp CASCADE ;\par CREATE TABLE emp (\par emp_id int not null primary key,\par designation text not null,\par location varchar(50) not null,\par jdate date not null,\par ctc float not null\par \par )\par PARTITION BY RANGE (jdate, emp_id)\par (\par emp80 (START '01-01-1980',MINVALUE END '01-01-1990',500),\par emp90 (START '01-01-1990',500 END '01-01-2000',1500+10),\par emp00 (START '1 Jan 2000',1500+20 END 'Jan 1, 2010', MAXVALUE),\par DEFAULT\par );\par \par INSERT INTO emp VALUES (1, 'Project Manager', 'London', '01-01-1984', 80000);\par INSERT INTO emp VALUES (737, 'Business Analyst', 'Honolulu', '12-11-1994', 70000);\par INSERT INTO emp VALUES (1567, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO emp VALUES (1568, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO emp VALUES (3501, 'CEO', 'Pune', '17 June, 2006', 30000);\par INSERT INTO emp VALUES (3502, 'CEO', 'Pune', '17 June, 2006', 30000);\par INSERT INTO emp VALUES (3503, 'CEO', 'Pune', '17 June, 2006', 30000);\par INSERT INTO emp VALUES (1515, 'System Administrator', 'Chennai', '7 July, 2004', 35000);\par \par select * from emp;\par select * from emp80;\par select * from emp90;\par select * from emp00;\par \par ALTER TABLE emp DROP partition emp90;\par ALTER TABLE emp DROP partition (START minvalue,minvalue END '01-01-1990',500);\par \par INSERT INTO emp VALUES (1, 'Project Manager', 'London', '01-01-1984', 80000);\par INSERT INTO emp VALUES (737, 'Business Analyst', 'Honolulu', '12-11-1998', 70000);\par \par select * from emp;\par select * from emp80;\par select * from emp90;\par select * from emp00;\par \par ALTER TABLE emp ADD range partition emp90 (START '01-01-1990',500 END '01-01-1995',1000);\par \par INSERT INTO emp VALUES (1, 'Project Manager', 'London', '01-01-1984', 80000);\par INSERT INTO emp VALUES (737, 'Business Analyst', 'Honolulu', '12-11-1998', 70000);\par \par select * from emp;\par select * from emp90;\par select * from emp00;\par \par ALTER TABLE emp update partition emp90(START '01-01-1990',500 END '01-01-2000',1500+10);\par \par INSERT INTO emp VALUES (1, 'Project Manager', 'London', '01-01-1984', 80000);\par INSERT INTO emp VALUES (737, 'Business Analyst', 'Honolulu', '12-11-1998', 70000);\par \par select * from emp;\par select * from emp90;\par select * from emp00;\par \par ALTER TABLE emp split partition emp00 at values ('1 Jan, 2005', 2500) into emp1 and emp2;\par \par select * from emp;\par select * from emp90;\par select * from emp1;\par select * from emp2;\par \par ALTER TABLE emp merge partitions emp1 and emp2 into emp00;\par \par select * from emp;\par select * from emp90;\par select * from emp00;\par \par ALTER TABLE emp ADD range partition emp40 (START '01-01-2010',20000 END '01 JAn, 2050',30000);\par INSERT INTO emp VALUES (20000, 'SSE', 'Pune', '01-01-2045', 80000);\par \par ALTER TABLE emp ADD range partition emp80 (START '01-01-1980',1 END '01-01-1990',500);\par ALTER TABLE emp ADD range partition emp00 (START '1 Jan 2000',1500+20 END 'Jan 1, 2010', 4000-50);\par \par INSERT INTO emp VALUES (1, 'Project Manager', 'London', '01-01-1984', 80000);\par INSERT INTO emp VALUES (737, 'Business Analyst', 'Honolulu', '12-11-1998', 70000);\par INSERT INTO emp VALUES (1567, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO emp VALUES (1515, 'System Administrator', 'Chennai', '7 July, 1994', 35000);\par \par select * from emp;\par select * from emp80;\par select * from emp90;\par select * from emp00;\par \par UPDATE emp SET emp_id = 12000 where emp_id = 1;\par UPDATE emp SET jdate = '1995-10-9', emp_id = 750 where emp_id = 737;\par UPDATE emp SET jdate = '2003-01-01', emp_id = 1550 where emp_id = 120;\par \par select * from emp;\par select * from emp80;\par select * from emp90;\par select * from emp00;\par \par ALTER TABLE emp exchange emp00 with table a;\par \par INSERT INTO emp VALUES (123, 'CFO', 'New York', '1985-12-21', 60000);\par INSERT INTO emp VALUES (1232, 'CFO', 'New York', '1985-12-21', 60000);\par INSERT INTO emp VALUES (1567, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO emp VALUES (1515, 'System Administrator', 'Chennai', '7 July, 1994', 35000);\par \par \f1 ALTER TABLE emp ALTER COLUMN location TYPE varchar(100);\par ALTER TABLE emp alter column location set default 'indiana';\par \par \f0 SELECT * from pg_partitions;\f1\par \par \f0 select * from emp;\par select * from emp80;\par select * from emp90;\par select * from emp00;\par select * from a;\par \f1\par --\par \f0 -- Run the SQL commands in partition.sql(found in base directory) to setup trigger functions \par -- for partitioning. This file is generated by master makefile. \par -- psql -p xxxx < partition.sql\par --\par \f1\par \f0 -- pg_dump -Ft testdb -p 5454 > dump.tar\par -- pg_restore -p 5454 -C -d testdb dump.tar\par \par \f1 VACUUM VERBOSE emp;\par ANALYZE VERBOSE emp;\par TRUNCATE emp;\par \f0\par DROP TABLE emp CASCADE ;\par CREATE TABLE emp (\par emp_id int not null primary key,\par designation text not null,\par location varchar(50) not null unique,\par jdate date not null,\par ctc float not null\par )\par PARTITION BY HASH (location, jdate, emp_id) PARTITIONS 3;\par \par INSERT INTO emp VALUES (1, 'Project Manager', 'London', '01-01-1984', 80000);\par INSERT INTO emp VALUES (737, 'Business Analyst', 'Honolulu', '11-11-1998', 70000);\par INSERT INTO emp VALUES (1567, 'Programmer', 'Guangzhou', '7 Jan, 2001', 30000);\par INSERT INTO emp VALUES (1515, 'System Administrator', 'Chennai', '7 July, 1994', 35000);\par INSERT INTO emp VALUES (3949, 'Principal Architect', 'Pune', '25 Feb, 2010', 35000);\par \par select * from emp;\par select * from emp_0;\par select * from emp_1;\par select * from emp_2;\par \par ALTER TABLE emp update partitions to 1;\par \par select * from emp;\par select * from emp_0;\par select * from emp_1;\par select * from emp_2;\par \par ALTER TABLE emp update partitions to 3;\par \par select * from emp;\par select * from emp_0;\par select * from emp_1;\par select * from emp_2;\par \par UPDATE emp SET emp_id = 120 where emp_id = 1;\par UPDATE emp SET jdate = '1995-10-9', emp_id = 750 where emp_id = 737;\par UPDATE emp SET jdate = 'Feb 23, 2009' where jdate = '2001-01-07';\par UPDATE emp SET jdate = '1994-01-01' where emp_id = 120;\par UPDATE emp SET jdate = '1994-01-01', emp_id = 550 where emp_id = 120;\par \par select * from emp;\par select * from emp_0;\par select * from emp_1;\par select * from emp_2;\par \par INSERT INTO emp_0 VALUES (156, 'Accountant', 'Beijing', '2000-12-12', 40000);\par INSERT INTO emp_0 VALUES (1520, 'Accountant', 'Beijing', '2009-12-12', 40000);\par \par INSERT INTO emp_1 VALUES (652, 'CEO', 'Houston', '2000-01-02', 500000);\par INSERT INTO emp_1 VALUES (601, 'CEO', 'Houston', '1996-01-02', 500000);\par \par INSERT INTO emp_2 VALUES (1232, 'CFO', 'New York', '1985-12-21', 60000);\par INSERT INTO emp_2 VALUES (123, 'CFO', 'New York', '1985-12-21', 60000);\par \par ALTER TABLE emp ALTER COLUMN emp_id TYPE float;\par \par DROP TABLE emp cascade;\par }