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;
