Re: [SQL] [SOLVED] Postgres schema comparison.

Hi all,

If anyone is interested, here's the final solution
that I'm using to build a list of tables and their md5sums
based on what the psql interface queries when you do '\d [TABLE NAME]'

I attached the function I created, and this is the SQL I run :
select relname||':'||get_table_checksum(relname) from pg_class where relkind = 'r' and relname not like ('pg_%') and relname not like ('sql_%') order by relname;

This gives the same result for a specific table across all versions of postgres >= 7.3,
and runs for a minute or so for +- 450 tables on my machine.
It may break if you have some exotic definitions that I didn't test for,
but I think it's pretty solid as it is here.

Kind Regards
Stefan

Stef mentioned :
=> Here's my final solution that runs in less than a minute for +- 543 tables :
=> for x in $(psql -tc "select relname from pg_class where relkind = 'r' and relname not like 'pg_%'")
=> do
=> echo "$(psql -tc "select encode(digest('$(psql -c '\d '${x}'' mer9188_test | tr -d \"\'\")', 'md5'), 'hex')" mer9188_test | grep -v "^$"|tr -d " "):${x}"
=> done > compare_list.lst