Database Reference
In-Depth Information
table_name
,table_schema
,column_name;
This has output, such as the following:
table_schema | table_name | column_name | data_type
--------------+------------+-------------+-----------
s1 | x | col1 | smallint
s1 | x | col2 | text
s2 | x | col1 | integer
s2 | x | col3 | numeric
(4 rows)
How it works...
The definitions of tables are held within PostgreSQL, and can be accessed using the
Information Schema catalog views.
There might be valid reasons why the definitions differ. We've excluded PostgreSQL's own
internal tables, because there are similar names between the two catalogs: PostgreSQL's
implementation of the SQL Standard Information Schema and PostgreSQL's own internal
pg_catalog
schema.
Those queries are fairly complex. In fact, there is even more complexity we could add to those
queries to compare all sorts of other things like DEFAULT values or constraints. The basic idea
can be extended in various directions from.
There's more...
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 SQL
as
