ALTER TABLE tbl ADD COLUMN id_desc bigint; CREATE OR REPLACE FUNCTION zzz_tbl_autofill_desc() RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF NEW.id_desc IS NULL THEN NEW.id_desc := heerid_to_desc(NEW.id); END IF; ELSIF TG_OP = 'UPDATE' THEN IF NEW.id IS DISTINCT FROM OLD.id THEN NEW.id_desc := heerid_to_desc(NEW.id); ELSIF NEW.id_desc IS NULL THEN NEW.id_desc := heerid_to_desc(NEW.id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_tbl_autofill_desc ON tbl; CREATE TRIGGER zzz_tbl_autofill_desc BEFORE INSERT OR UPDATE ON tbl FOR EACH ROW EXECUTE FUNCTION zzz_tbl_autofill_desc(); CALL heeranjid_bulk_backfill('tbl', 'id', 'id_desc', 'heer', 10000) SELECT count(*) FROM tbl WHERE id_desc IS NULL CREATE UNIQUE INDEX CONCURRENTLY idx_tbl_id_desc ON tbl (id_desc) ALTER TABLE tbl ADD CONSTRAINT tbl_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE tbl VALIDATE CONSTRAINT tbl_id_desc_nn; ALTER TABLE tbl ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE tbl DROP CONSTRAINT tbl_id_desc_nn; ALTER TABLE tbl DROP CONSTRAINT tbl_pkey; ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY USING INDEX idx_tbl_id_desc; ALTER TABLE tbl ALTER COLUMN id_desc SET DEFAULT heerid_next_desc(); ALTER TABLE tbl ALTER COLUMN id DROP DEFAULT; ALTER TABLE tbl DROP COLUMN id; DROP TRIGGER zzz_tbl_autofill_desc ON tbl; DROP FUNCTION zzz_tbl_autofill_desc() CASCADE; ALTER TABLE tbl RENAME COLUMN id_desc TO id;
