PostgreSQL 파티셔닝 테이블 실 생성 사례

• 해당 항목에 필자가 실제 운영 서비스 내 생성한 파티셔닝 테이블 사례를 개시한다. 먼저 테이블 생성을 수행한다.
  
  
• 우선 생성 하려는 대상 테이블을 확인한다. (메타데이터 내 조회 쿼리참조)
  
  

 

• 그 후, 해당 서비스에서 기존에 파티션 테이블을 구성했던 테이블들을 정리하여 기존에 어디까지 파티션테이블이 생성되었는지 확인 후, 신규 생성 기간을 어디부터 시작할 지 확인 한다.
  
  
• Postgresql 에서는 테이블 생성, 인덱스 구성, 테이블들에 대한 권한 부여 순서대로 작업을 진행한다. 혹은 해당 내용들을 아래와 같이 스크립트로 생성 후, infile 개념으로 수행해도 된다.
  
  

----------------202102------------------------
-- Table: voc_202102

-- DROP TABLE voc_202102;

CREATE TABLE vital.voc_202102
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202102_created_time_check CHECK (created_time >= '2021-02-01'::date AND created_time < '2021-03-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202102
  OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202102 TO vital;
GRANT ALL ON TABLE vital.voc_202102 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202102 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202102 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202102 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202102 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202102 TO "SERVISEL";

-- Index: voc_202102_sr_created_dt

-- DROP INDEX voc_202102_sr_created_dt;

CREATE INDEX voc_202102_sr_created_dt
  ON vital.voc_202102
  USING btree
  (created_time);

----------------202103------------------------
-- Table: voc_202103

-- DROP TABLE voc_202103;

CREATE TABLE vital.voc_202103
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202103_created_time_check CHECK (created_time >= '2021-03-01'::date AND created_time < '2021-04-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202103 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202103 TO vital;
GRANT ALL ON TABLE vital.voc_202103 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202103 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202103 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202103 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202103 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202103 TO "SERVISEL";

-- Index: voc_202103_sr_created_dt

-- DROP INDEX voc_202103_sr_created_dt;

CREATE INDEX voc_202103_sr_created_dt
  ON vital.voc_202103
  USING btree
  (created_time);

----------------202104------------------------
-- Table: voc_202104

-- DROP TABLE voc_202104;

CREATE TABLE vital.voc_202104
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202104_created_time_check CHECK (created_time >= '2021-04-01'::date AND created_time < '2021-05-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202104 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202104 TO vital;
GRANT ALL ON TABLE vital.voc_202104 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202104 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202104 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202104 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202104 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202104 TO "SERVISEL";

-- Index: voc_202104_sr_created_dt

-- DROP INDEX voc_202104_sr_created_dt;

CREATE INDEX voc_202104_sr_created_dt
  ON vital.voc_202104
  USING btree
  (created_time);

----------------202105------------------------
-- Table: voc_202105

-- DROP TABLE voc_202105;

CREATE TABLE vital.voc_202105
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202105_created_time_check CHECK (created_time >= '2021-05-01'::date AND created_time < '2021-06-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202105 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202105 TO vital;
GRANT ALL ON TABLE vital.voc_202105 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202105 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202105 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202105 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202105 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202105 TO "SERVISEL";

-- Index: voc_202105_sr_created_dt

-- DROP INDEX voc_202105_sr_created_dt;

CREATE INDEX voc_202105_sr_created_dt
  ON vital.voc_202105
  USING btree
  (created_time);

----------------202106------------------------
-- Table: voc_202106

-- DROP TABLE voc_202106;

CREATE TABLE vital.voc_202106
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202106_created_time_check CHECK (created_time >= '2021-06-01'::date AND created_time < '2021-07-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202106 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202106 TO vital;
GRANT ALL ON TABLE vital.voc_202106 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202106 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202106 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202106 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202106 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202106 TO "SERVISEL";

-- Index: voc_202106_sr_created_dt

-- DROP INDEX voc_202106_sr_created_dt;

CREATE INDEX voc_202106_sr_created_dt
  ON vital.voc_202106
  USING btree
  (created_time);

----------------202107------------------------
-- Table: voc_202107

-- DROP TABLE voc_202107;

CREATE TABLE vital.voc_202107
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202107_created_time_check CHECK (created_time >= '2021-07-01'::date AND created_time < '2021-08-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202107 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202107 TO vital;
GRANT ALL ON TABLE vital.voc_202107 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202107 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202107 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202107 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202107 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202107 TO "SERVISEL";

-- Index: voc_202107_sr_created_dt

-- DROP INDEX voc_202107_sr_created_dt;

CREATE INDEX voc_202107_sr_created_dt
  ON vital.voc_202107
  USING btree
  (created_time);

----------------202108------------------------
-- Table: voc_202108

-- DROP TABLE voc_202108;

CREATE TABLE vital.voc_202108
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202108_created_time_check CHECK (created_time >= '2021-08-01'::date AND created_time < '2021-09-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202108 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202108 TO vital;
GRANT ALL ON TABLE vital.voc_202108 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202108 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202108 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202108 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202108 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202108 TO "SERVISEL";

-- Index: voc_202108_sr_created_dt

-- DROP INDEX voc_202108_sr_created_dt;

CREATE INDEX voc_202108_sr_created_dt
  ON vital.voc_202108
  USING btree
  (created_time);

----------------202109------------------------
-- Table: voc_202109

-- DROP TABLE voc_202109;

CREATE TABLE vital.voc_202109
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202109_created_time_check CHECK (created_time >= '2021-09-01'::date AND created_time < '2021-10-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202109 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202109 TO vital;
GRANT ALL ON TABLE vital.voc_202109 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202109 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202109 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202109 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202109 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202109 TO "SERVISEL";

-- Index: voc_202109_sr_created_dt

-- DROP INDEX voc_202109_sr_created_dt;

CREATE INDEX voc_202109_sr_created_dt
  ON vital.voc_202109
  USING btree
  (created_time);

----------------202110------------------------
-- Table: voc_202110

-- DROP TABLE voc_202110;

CREATE TABLE vital.voc_202110
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202110_created_time_check CHECK (created_time >= '2021-10-01'::date AND created_time < '2021-11-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202110 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202110 TO vital;
GRANT ALL ON TABLE vital.voc_202110 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202110 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202110 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202110 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202110 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202110 TO "SERVISEL";

-- Index: voc_202110_sr_created_dt

-- DROP INDEX voc_202110_sr_created_dt;

CREATE INDEX voc_202110_sr_created_dt
  ON vital.voc_202110
  USING btree
  (created_time);

----------------202111------------------------
-- Table: voc_202111

-- DROP TABLE voc_202111;

CREATE TABLE vital.voc_202111
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202111_created_time_check CHECK (created_time >= '2021-11-01'::date AND created_time < '2021-12-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202111 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202111 TO vital;
GRANT ALL ON TABLE vital.voc_202111 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202111 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202111 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202111 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202111 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202111 TO "SERVISEL";

-- Index: voc_202111_sr_created_dt

-- DROP INDEX voc_202111_sr_created_dt;

CREATE INDEX voc_202111_sr_created_dt
  ON vital.voc_202111
  USING btree
  (created_time);

----------------202112------------------------
-- Table: voc_202112

-- DROP TABLE voc_202112;

CREATE TABLE vital.voc_202112
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202112_created_time_check CHECK (created_time >= '2021-12-01'::date AND created_time < '2022-01-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202112 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202112 TO vital;
GRANT ALL ON TABLE vital.voc_202112 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202112 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202112 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202112 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202112 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202112 TO "SERVISEL";

-- Index: voc_202112_sr_created_dt

-- DROP INDEX voc_202112_sr_created_dt;

CREATE INDEX voc_202112_sr_created_dt
  ON vital.voc_202112
  USING btree
  (created_time);
----------------202201------------------------
-- Table: voc_202201

-- DROP TABLE voc_202201;

CREATE TABLE vital.voc_202201
(
-- 상속 from table voc:  voc_id bigint NOT NULL,
-- 상속 from table voc:  created_time timestamp without time zone,
-- 상속 from table voc:  creator_id character varying(64),
-- 상속 from table voc:  owner_id character varying(64),
-- 상속 from table voc:  row_id character varying(24) NOT NULL,
-- 상속 from table voc:  updated_time timestamp without time zone,
-- 상속 from table voc:  registed_time timestamp without time zone,
-- 상속 from table voc:  registed_target character varying(15),
-- 상속 from table voc:  eai_seq bigint DEFAULT 0,
  CONSTRAINT voc_202112_created_time_check CHECK (created_time >= '2022-01-01'::date AND created_time < '2022-02-01'::date)
)
INHERITS (vital.voc)
WITH (
  OIDS=FALSE
);
ALTER TABLE vital.voc_202201 OWNER TO vital;
GRANT ALL ON TABLE vital.voc_202201 TO vital;
GRANT ALL ON TABLE vital.voc_202201 TO vital_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202201 TO vital_user;
GRANT SELECT ON TABLE vital.voc_202201 TO vital_monitor;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202201 TO "H6805339";
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE vital.voc_202201 TO "H6826557";
GRANT SELECT ON TABLE vital.voc_202201 TO "SERVISEL";

-- Index: voc_202201_sr_created_dt

-- DROP INDEX voc_202201_sr_created_dt;

CREATE INDEX voc_202201_sr_created_dt
  ON vital.voc_202201
  USING btree
  (created_time);

 

• 테이블 생성을 마치면 이제 이와 관련된 프로시저(혹은 함수) 및 트리거를 생성한다. PostgreSQL 은 프로시저 소유 및 실행 권한까지 설정함을 유념한다.
  
  

CREATE OR REPLACE FUNCTION vital.voc_field_insert_trigger()
   RETURNS trigger
   LANGUAGE plpgsql
AS $function$

BEGIN
    IF ( NEW.sr_created_dt >= DATE '2014-01-01' AND NEW.sr_created_dt < DATE '2014-02-01' ) THEN
        INSERT INTO vital.voc_field_201401 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-02-01' AND NEW.sr_created_dt < DATE '2014-03-01' ) THEN
        INSERT INTO vital.voc_field_201402 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-03-01' AND NEW.sr_created_dt < DATE '2014-04-01' ) THEN
        INSERT INTO vital.voc_field_201403 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-04-01' AND NEW.sr_created_dt < DATE '2014-05-01' ) THEN
        INSERT INTO vital.voc_field_201404 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-05-01' AND NEW.sr_created_dt < DATE '2014-06-01' ) THEN
        INSERT INTO vital.voc_field_201405 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-06-01' AND NEW.sr_created_dt < DATE '2014-07-01' ) THEN
        INSERT INTO vital.voc_field_201406 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-07-01' AND NEW.sr_created_dt < DATE '2014-08-01' ) THEN
        INSERT INTO vital.voc_field_201407 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-08-01' AND NEW.sr_created_dt < DATE '2014-09-01' ) THEN
        INSERT INTO vital.voc_field_201408 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-09-01' AND NEW.sr_created_dt < DATE '2014-10-01' ) THEN
        INSERT INTO vital.voc_field_201409 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-10-01' AND NEW.sr_created_dt < DATE '2014-11-01' ) THEN
        INSERT INTO vital.voc_field_201410 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-11-01' AND NEW.sr_created_dt < DATE '2014-12-01' ) THEN
        INSERT INTO vital.voc_field_201411 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2014-12-01' AND NEW.sr_created_dt < DATE '2015-01-01' ) THEN
        INSERT INTO vital.voc_field_201412 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-01-01' AND NEW.sr_created_dt < DATE '2015-02-01' ) THEN
        INSERT INTO vital.voc_field_201501 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-02-01' AND NEW.sr_created_dt < DATE '2015-03-01' ) THEN
        INSERT INTO vital.voc_field_201502 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-03-01' AND NEW.sr_created_dt < DATE '2015-04-01' ) THEN
        INSERT INTO vital.voc_field_201503 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-04-01' AND NEW.sr_created_dt < DATE '2015-05-01' ) THEN
        INSERT INTO vital.voc_field_201504 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-05-01' AND NEW.sr_created_dt < DATE '2015-06-01' ) THEN
        INSERT INTO vital.voc_field_201505 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-06-01' AND NEW.sr_created_dt < DATE '2015-07-01' ) THEN
        INSERT INTO vital.voc_field_201506 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-07-01' AND NEW.sr_created_dt < DATE '2015-08-01' ) THEN
        INSERT INTO vital.voc_field_201507 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-08-01' AND NEW.sr_created_dt < DATE '2015-09-01' ) THEN
        INSERT INTO vital.voc_field_201508 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-09-01' AND NEW.sr_created_dt < DATE '2015-10-01' ) THEN
        INSERT INTO vital.voc_field_201509 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-10-01' AND NEW.sr_created_dt < DATE '2015-11-01' ) THEN
        INSERT INTO vital.voc_field_201510 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-11-01' AND NEW.sr_created_dt < DATE '2015-12-01' ) THEN
        INSERT INTO vital.voc_field_201511 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2015-12-01' AND NEW.sr_created_dt < DATE '2016-01-01' ) THEN
        INSERT INTO vital.voc_field_201512 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-01-01' AND NEW.sr_created_dt < DATE '2016-02-01' ) THEN
        INSERT INTO vital.voc_field_201601 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-02-01' AND NEW.sr_created_dt < DATE '2016-03-01' ) THEN
        INSERT INTO vital.voc_field_201602 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-03-01' AND NEW.sr_created_dt < DATE '2016-04-01' ) THEN
        INSERT INTO vital.voc_field_201603 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-04-01' AND NEW.sr_created_dt < DATE '2016-05-01' ) THEN
        INSERT INTO vital.voc_field_201604 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-05-01' AND NEW.sr_created_dt < DATE '2016-06-01' ) THEN
        INSERT INTO vital.voc_field_201605 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-06-01' AND NEW.sr_created_dt < DATE '2016-07-01' ) THEN
        INSERT INTO vital.voc_field_201606 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-07-01' AND NEW.sr_created_dt < DATE '2016-08-01' ) THEN
        INSERT INTO vital.voc_field_201607 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-08-01' AND NEW.sr_created_dt < DATE '2016-09-01' ) THEN
        INSERT INTO vital.voc_field_201608 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-09-01' AND NEW.sr_created_dt < DATE '2016-10-01' ) THEN
        INSERT INTO vital.voc_field_201609 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-10-01' AND NEW.sr_created_dt < DATE '2016-11-01' ) THEN
        INSERT INTO vital.voc_field_201610 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-11-01' AND NEW.sr_created_dt < DATE '2016-12-01' ) THEN
        INSERT INTO vital.voc_field_201611 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2016-12-01' AND NEW.sr_created_dt < DATE '2017-01-01' ) THEN
        INSERT INTO vital.voc_field_201612 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-01-01' AND NEW.sr_created_dt < DATE '2017-02-01' ) THEN
        INSERT INTO vital.voc_field_201701 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-02-01' AND NEW.sr_created_dt < DATE '2017-03-01' ) THEN
        INSERT INTO vital.voc_field_201702 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-03-01' AND NEW.sr_created_dt < DATE '2017-04-01' ) THEN
        INSERT INTO vital.voc_field_201703 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-04-01' AND NEW.sr_created_dt < DATE '2017-05-01' ) THEN
        INSERT INTO vital.voc_field_201704 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-05-01' AND NEW.sr_created_dt < DATE '2017-06-01' ) THEN
        INSERT INTO vital.voc_field_201705 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-06-01' AND NEW.sr_created_dt < DATE '2017-07-01' ) THEN
        INSERT INTO vital.voc_field_201706 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-07-01' AND NEW.sr_created_dt < DATE '2017-08-01' ) THEN
        INSERT INTO vital.voc_field_201707 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-08-01' AND NEW.sr_created_dt < DATE '2017-09-01' ) THEN
        INSERT INTO vital.voc_field_201708 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-09-01' AND NEW.sr_created_dt < DATE '2017-10-01' ) THEN
        INSERT INTO vital.voc_field_201709 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-10-01' AND NEW.sr_created_dt < DATE '2017-11-01' ) THEN
        INSERT INTO vital.voc_field_201710 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-11-01' AND NEW.sr_created_dt < DATE '2017-12-01' ) THEN
        INSERT INTO vital.voc_field_201711 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2017-12-01' AND NEW.sr_created_dt < DATE '2018-01-01' ) THEN
        INSERT INTO vital.voc_field_201712 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-01-01' AND NEW.sr_created_dt < DATE '2018-02-01' ) THEN
        INSERT INTO vital.voc_field_201801 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-02-01' AND NEW.sr_created_dt < DATE '2018-03-01' ) THEN
        INSERT INTO vital.voc_field_201802 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-03-01' AND NEW.sr_created_dt < DATE '2018-04-01' ) THEN
        INSERT INTO vital.voc_field_201803 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-04-01' AND NEW.sr_created_dt < DATE '2018-05-01' ) THEN
        INSERT INTO vital.voc_field_201804 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-05-01' AND NEW.sr_created_dt < DATE '2018-06-01' ) THEN
        INSERT INTO vital.voc_field_201805 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-06-01' AND NEW.sr_created_dt < DATE '2018-07-01' ) THEN
        INSERT INTO vital.voc_field_201806 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-07-01' AND NEW.sr_created_dt < DATE '2018-08-01' ) THEN
        INSERT INTO vital.voc_field_201807 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-08-01' AND NEW.sr_created_dt < DATE '2018-09-01' ) THEN
        INSERT INTO vital.voc_field_201808 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-09-01' AND NEW.sr_created_dt < DATE '2018-10-01' ) THEN
        INSERT INTO vital.voc_field_201809 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-10-01' AND NEW.sr_created_dt < DATE '2018-11-01' ) THEN
        INSERT INTO vital.voc_field_201810 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-11-01' AND NEW.sr_created_dt < DATE '2018-12-01' ) THEN
        INSERT INTO vital.voc_field_201811 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2018-12-01' AND NEW.sr_created_dt < DATE '2019-01-01' ) THEN
        INSERT INTO vital.voc_field_201812 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-01-01' AND NEW.sr_created_dt < DATE '2019-02-01' ) THEN
        INSERT INTO vital.voc_field_201901 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-02-01' AND NEW.sr_created_dt < DATE '2019-03-01' ) THEN
        INSERT INTO vital.voc_field_201902 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-03-01' AND NEW.sr_created_dt < DATE '2019-04-01' ) THEN
        INSERT INTO vital.voc_field_201903 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-04-01' AND NEW.sr_created_dt < DATE '2019-05-01' ) THEN
        INSERT INTO vital.voc_field_201904 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-05-01' AND NEW.sr_created_dt < DATE '2019-06-01' ) THEN
        INSERT INTO vital.voc_field_201905 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-06-01' AND NEW.sr_created_dt < DATE '2019-07-01' ) THEN
        INSERT INTO vital.voc_field_201906 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-07-01' AND NEW.sr_created_dt < DATE '2019-08-01' ) THEN
        INSERT INTO vital.voc_field_201907 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-08-01' AND NEW.sr_created_dt < DATE '2019-09-01' ) THEN
        INSERT INTO vital.voc_field_201908 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-09-01' AND NEW.sr_created_dt < DATE '2019-10-01' ) THEN
        INSERT INTO vital.voc_field_201909 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-10-01' AND NEW.sr_created_dt < DATE '2019-11-01' ) THEN
        INSERT INTO vital.voc_field_201910 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-11-01' AND NEW.sr_created_dt < DATE '2019-12-01' ) THEN
        INSERT INTO vital.voc_field_201911 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2019-12-01' AND NEW.sr_created_dt < DATE '2020-01-01' ) THEN
        INSERT INTO vital.voc_field_201912 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-01-01' AND NEW.sr_created_dt < DATE '2020-02-01' ) THEN
        INSERT INTO vital.voc_field_202001 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-02-01' AND NEW.sr_created_dt < DATE '2020-03-01' ) THEN
        INSERT INTO vital.voc_field_202002 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-03-01' AND NEW.sr_created_dt < DATE '2020-04-01' ) THEN
        INSERT INTO vital.voc_field_202003 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-04-01' AND NEW.sr_created_dt < DATE '2020-05-01' ) THEN
        INSERT INTO vital.voc_field_202004 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-05-01' AND NEW.sr_created_dt < DATE '2020-06-01' ) THEN
        INSERT INTO vital.voc_field_202005 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-06-01' AND NEW.sr_created_dt < DATE '2020-07-01' ) THEN
        INSERT INTO vital.voc_field_202006 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-07-01' AND NEW.sr_created_dt < DATE '2020-08-01' ) THEN
        INSERT INTO vital.voc_field_202007 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-08-01' AND NEW.sr_created_dt < DATE '2020-09-01' ) THEN
        INSERT INTO vital.voc_field_202008 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-09-01' AND NEW.sr_created_dt < DATE '2020-10-01' ) THEN
        INSERT INTO vital.voc_field_202009 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-10-01' AND NEW.sr_created_dt < DATE '2020-11-01' ) THEN
        INSERT INTO vital.voc_field_202010 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-11-01' AND NEW.sr_created_dt < DATE '2020-12-01' ) THEN
        INSERT INTO vital.voc_field_202011 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2020-12-01' AND NEW.sr_created_dt < DATE '2021-01-01' ) THEN
        INSERT INTO vital.voc_field_202012 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-01-01' AND NEW.sr_created_dt < DATE '2021-02-01' ) THEN
        INSERT INTO vital.voc_field_202101 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-02-01' AND NEW.sr_created_dt < DATE '2021-03-01' ) THEN
        INSERT INTO vital.voc_field_202102 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-03-01' AND NEW.sr_created_dt < DATE '2021-04-01' ) THEN
        INSERT INTO vital.voc_field_202103 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-04-01' AND NEW.sr_created_dt < DATE '2021-05-01' ) THEN
        INSERT INTO vital.voc_field_202104 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-05-01' AND NEW.sr_created_dt < DATE '2021-06-01' ) THEN
        INSERT INTO vital.voc_field_202105 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-06-01' AND NEW.sr_created_dt < DATE '2021-07-01' ) THEN
        INSERT INTO vital.voc_field_202106 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-07-01' AND NEW.sr_created_dt < DATE '2021-08-01' ) THEN
        INSERT INTO vital.voc_field_202107 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-08-01' AND NEW.sr_created_dt < DATE '2021-09-01' ) THEN
        INSERT INTO vital.voc_field_202108 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-09-01' AND NEW.sr_created_dt < DATE '2021-10-01' ) THEN
        INSERT INTO vital.voc_field_202109 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-10-01' AND NEW.sr_created_dt < DATE '2021-11-01' ) THEN
        INSERT INTO vital.voc_field_202110 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-11-01' AND NEW.sr_created_dt < DATE '2021-12-01' ) THEN
        INSERT INTO vital.voc_field_202111 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2021-12-01' AND NEW.sr_created_dt < DATE '2022-01-01' ) THEN
        INSERT INTO vital.voc_field_202112 VALUES (NEW.*);
    ELSIF ( NEW.sr_created_dt >= DATE '2022-01-01' AND NEW.sr_created_dt < DATE '2022-02-01' ) THEN
        INSERT INTO vital.voc_field_202201 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the voc_field_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$function$;

ALTER FUNCTION vital.voc_field_insert_trigger()  OWNER TO vital;
GRANT EXECUTE ON FUNCTION vital.voc_field_insert_trigger() TO public;
GRANT EXECUTE ON FUNCTION vital.voc_field_insert_trigger() TO vital_admin;
GRANT EXECUTE ON FUNCTION vital.voc_field_insert_trigger() TO vital_user;
GRANT EXECUTE ON FUNCTION vital.voc_field_insert_trigger() TO vital;

 

• 그 후, 각 조건에 맞는 파티션 테이블에 데이터가 기입 되도록 트리거를 만들어 준다.
  
  

CREATE TRIGGER voc_insert_trigger BEFORE INSERT ON vital.voc FOR EACH ROW EXECUTE PROCDURE vital.voc_insert_trigger()

 

• 트리거까지 만들어졌으면 아래와 같이 파티션 테이블 및 트리거가 연결 되었는지 테이블 속성을 확인한다.
  
  

 

• 그 후, pruning 테스트를 통하여 데이터가 조건에 맞게 파티션 테이블에 기입이 되는지 확인 한다.
  
  

-- 프루닝 테스트 insert 쿼리문 
insert into vital.voc values ('464433348', '2021-01-01', '118182484', '118182484', '1-1L60X08N', '2021-01-01', '2021-01-01', 'data-collector', '518464526');
insert into vital.voc values ('564433348', '2021-02-01', '218182484', '218182484', '2-1L60X08N', '2021-02-01', '2021-02-01', 'eata-collector', '618464526');
insert into vital.voc values ('464433348', '2021-03-01', '318182484', '318182484', '3-1L60X08N', '2021-03-01', '2021-03-01', 'fata-collector', '718464526');
insert into vital.voc values ('764433348', '2021-04-01', '418182484', '418182484', '4-1L60X08N', '2021-04-01', '2021-04-01', 'gata-collector', '818464526');
insert into vital.voc values ('864433348', '2021-05-01', '518182484', '518182484', '5-1L60X08N', '2021-05-01', '2021-05-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-06-01', '518182484', '518182484', '5-1L60X08N', '2021-06-01', '2021-06-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-07-01', '518182484', '518182484', '5-1L60X08N', '2021-07-01', '2021-07-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-08-01', '518182484', '518182484', '5-1L60X08N', '2021-08-01', '2021-08-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-09-01', '518182484', '518182484', '5-1L60X08N', '2021-09-01', '2021-09-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-10-01', '518182484', '518182484', '5-1L60X08N', '2021-10-01', '2021-10-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-11-01', '518182484', '518182484', '5-1L60X08N', '2021-11-01', '2021-11-01', 'hata-collector', '918464526');
insert into vital.voc values ('864433348', '2021-12-01', '518182484', '518182484', '5-1L60X08N', '2021-12-01', '2021-12-01', 'hata-collector', '918464526');
insert into vital.voc values ('964433348', '2022-01-01', '518182484', '518182484', '6-1L60X08N', '2022-01-01', '2022-01-01', 'hata-collector', '918464526');


-- 범위에 없는 값 insert 테스트
insert into vital.voc values ('964433348', '2022-02-01', '518182484', '518182484', '7-1L60X08N', '2022-01-01', '2022-02-01', 'hata-collector', '918464526');


-- 정상적으로 insert 되었는지 데이터 조회
select * from vital.voc_202101;
select * from vital.voc_202102;
select * from vital.voc_202103;
select * from vital.voc_202104;
select * from vital.voc_202105;
select * from vital.voc_202106;
select * from vital.voc_202107;
select * from vital.voc_202108;
select * from vital.voc_202109;
select * from vital.voc_202110;
select * from vital.voc_202111;
select * from vital.voc_202112;
select * from vital.voc_202201;


-- 프루닝 테스트 완료 후 insert 값 삭제 
delete from  vital.voc_202101 where created_time = '2021-01-01';
delete from  vital.voc_202102 where created_time = '2021-02-01';
delete from  vital.voc_202103 where created_time = '2021-03-01';
delete from  vital.voc_202104 where created_time = '2021-04-01';
delete from  vital.voc_202105 where created_time = '2021-05-01';
delete from  vital.voc_202106 where created_time = '2021-06-01';
delete from  vital.voc_202107 where created_time = '2021-07-01';
delete from  vital.voc_202108 where created_time = '2021-08-01';
delete from  vital.voc_202109 where created_time = '2021-09-01';
delete from  vital.voc_202110 where created_time = '2021-10-01';
delete from  vital.voc_202111 where created_time = '2021-11-01';
delete from  vital.voc_202112 where created_time = '2021-12-01';
delete from  vital.voc_202201 where created_time = '2022-01-01';


-- 정상적으로 데이터 삭제되었는지 확인
select * from vital.voc_202101;
select * from vital.voc_202102;
select * from vital.voc_202103;
select * from vital.voc_202104;
select * from vital.voc_202105;
select * from vital.voc_202106;
select * from vital.voc_202107;
select * from vital.voc_202108;
select * from vital.voc_202109;
select * from vital.voc_202110;
select * from vital.voc_202111;
select * from vital.voc_202112;
select * from vital.voc_202201;