Partitioning of tables in PostgreSQL

SELECT tl_pdm_id, tl_tracer, tl_pay_date_time, count (*) as duplication_count
FROM control.translog
group by tl_pdm_id, tl_tracer, tl_pay_date_time
HAVING count (*) > 1
LIMIT 10

DELETE FROM control.translog a USING control.translog b
WHERE a.tl_id < b.tl_id
AND a.tl_pdm_id = b.tl_pdm_id AND a.tl_pay_date_time = b.tl_pay_date_time AND a.tl_tracer=b.tl_tracer;

DO
$$
DECLARE
	pay_group record;
BEGIN
	-- Loop over payment id groups having same PDM and timestamp.
	FOR pay_group IN
		-- Find payments groups where multiple payment was done within same time
		WITH pay_group AS (
			SELECT tl_pdm_id as pdm_id, tl_pay_date_time as pay_ts, count (*) as duplication_count
			FROM control.translog
			group by tl_pdm_id, tl_pay_date_time
			HAVING count (*) > 1
		)
		-- We need to adjust all payments within tis minute. Re-group by minute start of minute
		SELECT pdm_id, date_trunc('minute', pay_ts) AS pay_ts_from
		FROM pay_group
		GROUP BY pdm_id, date_trunc('minute', pay_ts)
	LOOP
		-- Update each group by setting seconds, based on order (by tracer) to achive unique time stamp for each payment
		-- Update from select: https://stackoverflow.com/questions/6256610/updating-table-rows-in-postgres-using-subquery
		UPDATE control.translog
		SET tl_pay_date_time = subq.new_ts
		FROM(
			-- Prepare modified timestamps - base minute + number of seconds from order.
			SELECT tl_id as pay_id, date_trunc('minute', tl_pay_date_time) + (INTERVAL '1 second' * RANK() OVER w) as new_ts
			FROM control.translog
			WHERE tl_pdm_id = pay_group.pdm_id
			AND tl_pay_date_time >= pay_group.pay_ts_from
			AND tl_pay_date_time < pay_group.pay_ts_from + INTERVAL '1 minute'
			WINDOW w AS (PARTITION BY tl_pdm_id ORDER BY tl_tracer)
		) AS subq
		WHERE tl_id = subq.pay_id;
	END LOOP;
END;
$$;

insert INTO control.payment_2023_h2
(select * from control.payment_2023 WHERE pay_ts >= '2023-07-01+00');

DELETE FROM control.payment_2023 WHERE tl_pay_date_time >= '2023-07-01+00';

ALTER TABLE control.payment ATTACH PARTITION control.payment_2023 FOR VALUES FROM ('2023-01-01+00') TO ('2023-08-01+00');

ALTER TABLE control.payment RENAME TO payment2;

ALTER TABLE control.payment2 RENAME CONSTRAINT pk_payment TO pk_payment2;

CREATE TABLE control.payment
(
    pay_ts timestamp(0) with time zone NOT NULL,
    pdm_id integer NOT NULL,
    amount numeric(8,2),
    currency character varying(3),
    expiration_ts timestamp with time zone,
    ticket_nr integer,
    pay_reason control.payment_reason,
    pay_type control.payment_type,
    tracer integer,
    lpn character varying(255),
    psn integer,
    card_nr character varying(255),
    card_code integer,
    card_type smallint,
    auth_code character varying,
    tariff_info_id integer,
    rtp_session integer,
    custom_data jsonb,
    server_ts timestamp with time zone,
    CONSTRAINT pk_payment PRIMARY KEY (pay_ts, pdm_id)
)
-- Partitions by payment timestamp
PARTITION BY RANGE (pay_ts);

-- Add partitions as needed. For example
CREATE TABLE control."payment_2000_2019" PARTITION OF control.payment FOR VALUES FROM ('2000-01-01 +00') TO ('2020-01-01 +00');
CREATE TABLE control."payment_2020_2021" PARTITION OF control.payment FOR VALUES FROM ('2020-01-01 +00') TO ('2022-01-01 +00');
CREATE TABLE control."payment_2022" PARTITION OF control.payment FOR VALUES FROM ('2022-01-01 +00') TO ('2023-01-01 +00');
CREATE TABLE control."payment_2023" PARTITION OF control.payment FOR VALUES FROM ('2023-01-01 +00') TO ('2024-01-01 +00');
CREATE TABLE control."payment_2024" PARTITION OF control.payment FOR VALUES FROM ('2024-01-01 +00') TO ('2025-01-01 +00');
-- Default partition for payments which do not match any existing partition.
CREATE TABLE control."payment_default" PARTITION OF control.payment DEFAULT;

INSERT INTO control.payment
(
	SELECT
		pay_ts, pdm_id, amount, currency, expiration_ts, ticket_nr, pay_reason, pay_type, tracer, lpn,
		psn, card_nr, card_code, card_type, auth_code, tariff_info_id, rtp_session, custom_data, server_ts
	FROM control.payment2
);

-- Constraints
ALTER TABLE control.payment2 DROP CONSTRAINT fk_payment__payment_rtp;
ALTER TABLE control.payment ADD CONSTRAINT fk_payment__payment_rtp FOREIGN KEY (rtp_session) REFERENCES control.payment_rtp (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE control.payment2 DROP CONSTRAINT fk_payment__pdm;
ALTER TABLE control.payment ADD CONSTRAINT fk_payment__pdm FOREIGN KEY (pdm_id) REFERENCES control.pdm (pdm_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE control.payment2 DROP CONSTRAINT fk_payment__tariff_info;
ALTER TABLE control.payment ADD CONSTRAINT fk_payment__tariff_info FOREIGN KEY (tariff_info_id) REFERENCES control.tariff_info (tai_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE control.bonus_disposable DROP CONSTRAINT fk_bonus_disposable__payment;
ALTER TABLE control.bonus_disposable ADD CONSTRAINT fk_bonus_disposable__payment FOREIGN KEY (pay_ts, pay_pdm_id) REFERENCES control.payment (pay_ts, pdm_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE control.payment_bonus DROP CONSTRAINT fk_payment_bonus__payment;
ALTER TABLE control.payment_bonus ADD CONSTRAINT fk_payment_bonus__payment FOREIGN KEY (pay_ts, pay_pdm_id) REFERENCES control.payment (pay_ts, pdm_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE control.payment_ext DROP CONSTRAINT "fk_payment_ext__payment-base";
ALTER TABLE control.payment_ext ADD CONSTRAINT "fk_payment_ext__payment-base" FOREIGN KEY (base_pdm_id, base_pay_ts) REFERENCES control.payment (pdm_id, pay_ts) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE control.payment_ext DROP CONSTRAINT "fk_payment_ext__payment-ext";
ALTER TABLE control.payment_ext ADD CONSTRAINT "fk_payment_ext__payment-ext" FOREIGN KEY (ext_pdm_id, ext_pay_ts) REFERENCES control.payment (pdm_id, pay_ts) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE control.payment_recent DROP CONSTRAINT fk_payment_recent__payment;
ALTER TABLE control.payment_recent ADD CONSTRAINT fk_payment_recent__payment FOREIGN KEY (pay_ts, pdm_id) REFERENCES control.payment (pay_ts, pdm_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

-- Indexes
DROP INDEX control.fki_payment__pdm;
CREATE INDEX IF NOT EXISTS fki_payment__pdm ON control.payment USING btree (pdm_id ASC NULLS LAST);

DROP INDEX control.fki_payment__rtp;
CREATE INDEX IF NOT EXISTS fki_payment__rtp ON control.payment USING btree (rtp_session ASC NULLS LAST);

DROP TABLE control.payment2;