满分为@a-horse_with*_no_name,我发布的答复是为了完整性:
检查行数
首先让我们创建一些数据来测试查询:
create schema if not exists data;
create table if not exists data.test1 (nothing int);
create table if not exists data.test2 (nothing int);
insert into data.test1 (nothing)
(select random() from generate_series(1,28));
insert into data.test2 (nothing)
(select random() from generate_series(1,55));
create table if not exists public.expected_row_count (
table_schema varchar not null default '',table_name varchar not null default '',row_count bigint not null default 0
);
insert into public.expected_row_count (table_schema,table_name,row_count) values
('data','test1',(select count(*) from data.test1)),('data','test2',(select count(*) from data.test2))
;
现在查询以检查数据:
select * from (
select
table_schema,(xpath('/row/cnt/text()',xml_count))[1]::text::int as row_count
from (
select
table_schema,query_to_xml(format('select count(*) as cnt from %I.%I',table_schema,table_name),false,true,'') as xml_count
from information_schema.tables
where table_schema = 'data' --<< change here for the schema you want
) infs ) as r
inner join expected_row_count erc
on r.table_schema = erc.table_schema
and r.table_name = erc.table_name
and r.row_count != erc.row_count
;
如果所有计数都正确,则上一个查询应给出空结果,并且
缺少数据的表(如果没有)。要检查它,请更新一些计数
表放在expected_row_count
上,然后重新运行查询。例如:
update expected_row_count set row_count = 666 where table_name = 'test1';
本文链接:https://www.f2er.com/3099053.html