How to Get a Hash of an Entire Table in Postgresql

How can I get a hash of an entire table in postgresql?

just do like this to create a hash table aggregation function.

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);

then you could use the pg_concat function to caculate the table's hash value.

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

How to hash an entire table in postgres?

You can do this from the command line (replacing of course my_database and my_table):

psql my_database -c 'copy my_table to stdout' |sha1sum

If you want to use a query to limit columns, add ordering, etc., just modify the query:

psql my_database -c 'copy (select * from my_table order by my_id_column) to stdout' |sha1sum

Note that this does not hash anything except the column data. No schema information, constraints, indexes, metadata, permissions, etc.

Note also that sha1sum is an arbitrary hashing program; you can pipe this to any program that generates a hash. Some cuspy options are sha256sum and md5sum.

Finding the hash value of a row in postgresql

Cast the row to text and use md5 to make a hash:

SELECT
    md5(CAST((f.*)AS text))
FROM
    foo f;

Finding out the hash value of a group of rows

The simplest way - just concat all the string form md5 with string_agg. But to use this aggregate correctly you must specify ORDER BY.

Or use md5(string_agg(md5(CAST((f.*)AS text)),'')) with some ORDER BY - it will change if any field of f.* changes and it is cheap to compare.

postgres, how to put all the content of a table in a hash?

Just initialize your hash with the table name and an empty array right away:

def table_content_to_hash(table)
  hash = { table => [] }

  rows = @pg_conn.exec "SELECT * FROM #{table}";
  rows.each do |row|
    hash[table] << child
  end

  puts hash
end

How to generate a hash of the result set in Postgress?

You could use md5:

 log_table="
SELECT 
  md5(column1 || column2 || column3) AS hash,
  column1, column2, column3
FROM log_table where to_char(timestamp, '$ts_format') = '$tx_moment'";

How to Query a Ordered Hash Table (PostgreSQL)

You can simply use in:

select id
from items i
where tags_id in ('linux', 'computer')
group by id
order by sum(relationship) desc;

Unless you want all columns, then you can use the query above in cte and join:

with tmp as (
    select id
    from items
    where tags_id in ('linux', 'computer')
    group by id
    order by sum(relationship) desc
)
select i.*
from tmp t
inner join items i on t.id = i.id;