Check if two tables have the same structure (are compatible)

Question

I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense that I can perform set operations such as UNION ALL and EXCEPT on them?

I searched around on DBA.SE and elsewhere, and can only find questions about finding whether the contents of two tables are different, (e.g. Checking whether two tables have identical content in PostgreSQL), or when the compatibility is known (e.g. Compare two tables with same structure, but differing membership numbers). But I'm interested in checking the compatibility of table structure.

I'm using PostgreSQL 10.3, but standard compliant ways are certainly preferable.

There is a lot of problems to perform that task automatically. 1) Fields can have different names, so You cannot identify is field of some type matched the field1 or field2 of the same type without additional info about that fields. 2) UNION ALL and EXCEPT do not require to use all of table's fields, You can select only a part of them while reminders maybe not equivalent. To obtain table structures is not difficult while to compare them automatically may be unsolvable. — Akina, May 17 '18 at 06:02
Are you referring to the expansion of * (inclusive of all columns). Or compatability of a subset of the columns based on their type. — Evan Carroll, May 17 '18 at 17:47

bigfoot · Accepted Answer · 2018-05-17T10:35:09.330

Having example schema like:

create table table1 (
  id integer,
  txt text,
  col1 integer,
  col2 integer);

create table table2 (
  id integer,
  txt text,
  col1 text,
  colx integer);

and assuming you don't care about column names you can check potential conflicts in union-ed columns with query:

(select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table1'
except
select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table2')
union all
(select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table2'
except
select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table1'
)
order by 2;

The result will show offending column's position in tables' structure and data type.

data_type   ordinal_position
integer     3
text    3

If you have this shortened list you can check details in information_schema.columns disctionary to check details about each column.

score 3 · Answer 2 · answered May 17 '18 at 12:30

3

I think the best way is just to try it and see.

select * from (
    (select * from pgbench_accounts limit 0) 
    union all
    (select * from pgbench_history limit 0)
) as foo

Either it throws an error, or it doesn't.

answered May 17 '18 at 12:30

jjanes

39,726
3
37
48

David Spillett · Answer 3 · 2018-05-17T09:29:17.370

The main, standard, way to pull information about an SQL database from the database itself is the INFORMATION_SCHEMA schema, though you'll probably need to write this specific query yourself.

In this case information_schema.tables will be key (see https://www.postgresql.org/docs/current/static/infoschema-columns.html for documentation). The simplest version will be that the tables are compatible if for every column in table 1 there is a corresponding column with the same ordinal_position and data_type in table 2, and the number of columns in each is the same (so there are no extras in table 2).

To get more advanced you may have to start being more fuzzy about the type match: different string types (char, varchar, text) with different attributes (size, collation) may be compatible for instance, and some DBs will even let you UNION numeric and string types by implicitly converting the numeric^[1], and so forth.

^{[1] I'm not a postgres man so can't say if this is the case here, but it is easy to test. MS SQL Server tries to be halfway helpful but coerces towards the numeric type so SELECT mixedtype = 1 UNION SELECT mixedtype = '2' works but SELECT mixedtype = 'a' UNION SELECT mixedtype = 2 doesn't as a can't be coerced into an integer - so for safety I would assume string and numeric types are never compatible.}

score 2 · Answer 4 · answered Jun 29 '22 at 19:51

Following on accepted answer - if you want to know which column names are an issue, use the following:

SELECT column_name, data_type, ordinal_position
  FROM information_schema.columns
 WHERE table_schema = 'some_schema'
   AND table_name   = 'table_a'
except
SELECT column_name, data_type, ordinal_position
  FROM information_schema.columns
 WHERE table_schema = 'some_schema'
   AND table_name   = 'table_b'

If you only care about name differences, use SELECT column_name only and don't include the other 2 columns.

score 1 · Answer 5 · answered May 17 '18 at 08:19

If the field names are expected to be the same, then one option is to script out each table to a file, and then use a file comparison tool (such as WinMerge) to compare the two scripts side by side. Any differences in the structure will be immediately obvious.

Check if two tables have the same structure (are compatible)

5 Answers5