如何获取postgresql中整个表的哈希值?

就这样创建一个哈希表聚合函数。

create function pg_concat( text, text ) returns text as '
begin
    if $1 isnull then
        return $2;
    else
       return $1 || $2;
    end if;
end;' language 'plpgsql';

create function pg_concat_fin(text) returns text as '
begin
    return $1;
end;' language 'plpgsql';

create aggregate pg_concat (
    basetype = text,
    sfunc = pg_concat,
    stype = text,
    finalfunc = pg_concat_fin);

然后可以使用pg_concat函数计算表的散列值。

select md5(pg_concat(md5(CAST((f.*)AS text)))) from f order by id

我知道这是个老问题,但这是我的解决办法:

SELECT        
    md5(CAST((array_agg(f.* order by id))AS text)) /* id is a primary key of table (to avoid random sorting) */
FROM
    foo f; 

SELECT md5(array_agg(md5((t.*)::varchar))::varchar)
  FROM (
        SELECT *
          FROM my_table
         ORDER BY 1
       ) AS t

我有一个类似的要求,在测试专用表复制解决方案时使用。

第一个(在其他一些答案中提到)是,您需要确保在您检查的表上以已知顺序执行聚合。它的语法是例如。

select zz_hashagg(CAST((example.*)AS text) order by id) from example;

注意 order by

第二是使用 CAST((example.*)AS text 对于具有相同列内容的两个表,将不会给出相同的结果,除非这些列是以相同的顺序创建的。在我的例子中,这是不能保证的,所以为了得到一个真正的比较,我必须分别列出列,例如:

select zz_hashagg(CAST((example.id, example.a, example.c)AS text) order by id) from example;

create function zz_concat(text, text) returns text as 
    'select md5($1 || $2);' language 'sql';

create aggregate zz_hashagg(text) (
    sfunc = zz_concat,
    stype = text,
    initcond = '');

至于算法,您可以对所有单独的MD5散列进行异或运算,或者将它们连接起来并对连接进行散列运算。

如果你想完全在服务器端完成这项工作,你可能必须 create your own aggregation function ,然后你可以打电话给他。

select my_table_hash(md5(CAST((f.*)AS text)) from f order by id 

作为中间步骤,不必将整个表复制到客户机,只需为所有行选择MD5结果,然后通过md5sum运行这些结果。

无论哪种方式,您都需要建立一个固定的排序顺序,否则即使对于相同的数据,您也可能得到不同的校验和。

很好的答案。

无论如何,如果有人不需要使用聚合函数,而是需要维护对大小为几个GiB的表的支持,您可以使用这个 小的

CREATE OR REPLACE FUNCTION table_md5(
  table_name CHARACTER VARYING
  , VARIADIC order_key_columns CHARACTER VARYING [])
RETURNS CHARACTER VARYING AS $$
DECLARE
  order_key_columns_list CHARACTER VARYING;
  query CHARACTER VARYING;
  first BOOLEAN;
  i SMALLINT;
  working_cursor REFCURSOR;
  working_row_md5 CHARACTER VARYING;
  partial_md5_so_far CHARACTER VARYING;
BEGIN
  order_key_columns_list := '';

  first := TRUE;
  FOR i IN 1..array_length(order_key_columns, 1) LOOP
    IF first THEN
      first := FALSE;
    ELSE
      order_key_columns_list := order_key_columns_list || ', ';
    END IF;
    order_key_columns_list := order_key_columns_list || order_key_columns[i];
  END LOOP;

  query := (
    'SELECT ' ||
      'md5(CAST(t.* AS TEXT)) ' ||
    'FROM (' ||
      'SELECT * FROM ' || table_name || ' ' ||
      'ORDER BY ' || order_key_columns_list ||
    ') t');

  OPEN working_cursor FOR EXECUTE (query);
  -- RAISE NOTICE 'opened cursor for query: ''%''', query;

  first := TRUE;
  LOOP
    FETCH working_cursor INTO working_row_md5;
    EXIT WHEN NOT FOUND;
    IF first THEN 
      SELECT working_row_md5 INTO partial_md5_so_far;
    ELSE 
      SELECT md5(working_row_md5 || partial_md5_so_far)
      INTO partial_md5_so_far;
    END IF;
    -- RAISE NOTICE 'partial md5 so far: %', partial_md5_so_far;
  END LOOP;

  -- RAISE NOTICE 'final md5: %', partial_md5_so_far;
  RETURN partial_md5_so_far :: CHARACTER VARYING;
END;
$$ LANGUAGE plpgsql;

用作:

SELECT table_md5(
  'table_name', 'sorting_col_0', 'sorting_col_1', ..., 'sorting_col_n'
);