Postgres partition 設定 パーティション設定

create table ssr002_part  ・・・・

 最大日付を取得

SELECT MAX(GREATEST(
    COALESCE(stime_updatetime, '00000000000000'),
    COALESCE(xtime_updatetime, '00000000000000'),
    COALESCE(ztime_updatetime, '00000000000000')
)) AS latest_updatetime
FROM ssr002_no_part
WHERE
    stime_updatetime >= TO_CHAR(NOW() - INTERVAL '1 day', 'YYYYMMDDHH24MISS')
    OR xtime_updatetime >= TO_CHAR(NOW() - INTERVAL '1 day', 'YYYYMMDDHH24MISS')
    OR ztime_updatetime >= TO_CHAR(NOW() - INTERVAL '1 day', 'YYYYMMDDHH24MISS');

 

一回目
INSERT INTO ssr002_part SELECT * FROM ssr002;

ALTER TABLE arcsdbss.ssr002 RENAME TO ssr002_no_part;

ALTER TABLE arcsdbss.ssr002_part RENAME TO ssr002;

 

2回目 

INSERT INTO arcsdbss.ssr002 AS target
SELECT *
FROM arcsdbss.ssr002_no_part
where
rdate >='20250415'
ON CONFLICT (rdate, gcode, ccode, scode, rcode)
DO UPDATE SET
stime = EXCLUDED.stime,
xtime = EXCLUDED.xtime,
ztime = EXCLUDED.ztime,
stime_updatetime = EXCLUDED.stime_updatetime,
xtime_updatetime = EXCLUDED.xtime_updatetime,
ztime_updatetime = EXCLUDED.ztime_updatetime;

 

２'回目

INSERT INTO arcsdbss.ssr002 AS target
SELECT *
FROM arcsdbss.ssr002_no_part
WHERE
    stime_updatetime > '20250415233000'
    OR xtime_updatetime > '20250415233000'
    OR ztime_updatetime > '20250415233000'
ON CONFLICT (rdate, gcode, ccode, scode, rcode)
DO UPDATE SET
    stime = EXCLUDED.stime,
    xtime = EXCLUDED.xtime,
    ztime = EXCLUDED.ztime,
    stime_updatetime = EXCLUDED.stime_updatetime,
    xtime_updatetime = EXCLUDED.xtime_updatetime,
    ztime_updatetime = EXCLUDED.ztime_updatetime;

#------------------------------------------------------------------
SET client_encoding TO 'UTF8';
SET search_path = arcsdbss,public;

CREATE TABLE sst008_p202403 PARTITION OF sst008
FOR VALUES FROM ('20240301000000000') TO ('20240401000000000')
PARTITION BY HASH (gcode, ccode, scode);
CREATE TABLE sst008_sys_subp202403_0 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sst008_sys_subp202403_1 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE sst008_sys_subp202403_2 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE sst008_sys_subp202403_3 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE sst008_sys_subp202403_4 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE sst008_sys_subp202403_5 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE sst008_sys_subp202403_6 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE sst008_sys_subp202403_7 PARTITION OF sst008_p202403
FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE sst008_p202404 PARTITION OF sst008
FOR VALUES FROM ('20240401000000000') TO ('20240501000000000')
PARTITION BY HASH (gcode, ccode, scode);
CREATE TABLE sst008_sys_subp202404_0 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sst008_sys_subp202404_1 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE sst008_sys_subp202404_2 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE sst008_sys_subp202404_3 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE sst008_sys_subp202404_4 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE sst008_sys_subp202404_5 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE sst008_sys_subp202404_6 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE sst008_sys_subp202404_7 PARTITION OF sst008_p202404
FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE sst008_p202405 PARTITION OF sst008
FOR VALUES FROM ('20240501000000000') TO ('20240601000000000')
PARTITION BY HASH (gcode, ccode, scode);
CREATE TABLE sst008_sys_subp202405_0 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sst008_sys_subp202405_1 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE sst008_sys_subp202405_2 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE sst008_sys_subp202405_3 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE sst008_sys_subp202405_4 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE sst008_sys_subp202405_5 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE sst008_sys_subp202405_6 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE sst008_sys_subp202405_7 PARTITION OF sst008_p202405
FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE sst008_p202406 PARTITION OF sst008
FOR VALUES FROM ('20240601000000000') TO ('20240701000000000')
PARTITION BY HASH (gcode, ccode, scode);
CREATE TABLE sst008_sys_subp202406_0 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sst008_sys_subp202406_1 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE sst008_sys_subp202406_2 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE sst008_sys_subp202406_3 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE sst008_sys_subp202406_4 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE sst008_sys_subp202406_5 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE sst008_sys_subp202406_6 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE sst008_sys_subp202406_7 PARTITION OF sst008_p202406
FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE sst008_sys_subp202406_default PARTITION OF sst008 DEFAULT;