May 2018
Intermediate to advanced
576 pages
30h 25m
English
We can compare the definitions of any two tables using the following function:
CREATE OR REPLACE FUNCTION diff_table_definition(t1_schemaname text,t1_tablename text,t2_schemaname text,t2_tablename text)RETURNS TABLE(t1_column_name text,t1_data_type text,t2_column_name text,t2_data_type text)LANGUAGE SQLas$$SELECT t1.column_name,t1.data_type,t2.column_name,t2.data_typeFROM (SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 ) t1 FULL OUTER JOIN (SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = $3 AND table_name = $4 ) t2 ON t1.column_name = t2.column_name AND t1.data_type = t2.data_typeWHERE t1.column_name IS NULL OR t2.column_name IS NULL ...