Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table email_stats (
- id bigserial primary key,
- mailing_list_id int not null,
- sended_date timestamp not null,
- emails_send bigint not null default 0,
- emails_clicks bigint not null default 0
- );
- alter table email_stats
- add column emails_paid_clicks bigint not null default 0;
- set statement_timeout to '1s';
- alter table email_stats
- add column emails_paid_clicks bigint not null default 0;
- alter table email_stats
- add column emails_paid_clicks bigint not null default 0;
- begin;
- set local statement_timeout = '1s';
- alter table email_stats
- add column emails_paid_clicks bigint default null;
- alter table email_stats
- alter column emails_paid_clicks set default 0;
- commit;
- update email_stats
- set emails_paid_clicks = default
- where emails_paid_clicks is null
- and /* следующий кусочек данных */
- $current_id = (select min(id) from email_stats)
- $maxid = (select max(id) from email_stats)
- while ($current_id < $maxid) {
- $batch_to = $current_id + 10000 // максимальный размер пачки для одного обновления
- update email_stats
- set emails_paid_clicks = default
- where emails_paid_clicks is null
- and id >= $current_id and id < $batch_to
- $current_id = $batch_to
- sleep(5) -- задержка между обновлениями чтобы меньше мешать сервису
- }
- alter table email_stats alter column emails_paid_clicks set not null;
- begin;
- set local statement_timeout = '1s';
- alter table email_stats
- add constraint emails_paid_clicks_not_null
- check (emails_paid_clicks is not null) not valid;
- commit;
- alter table email_stats validate constraint emails_paid_clicks_not_null;
Add Comment
Please, Sign In to add comment