主页 / dba / 问题 / 206992
Accepted
tinlyx
Asked: 2018-05-17 21:36:25 +0800 CST

检查两个表是否具有相同的结构（兼容）

  • 772

我想知道是否有一种既定的方法来测试两个 PostgreSQL 表/关系是否具有相同的结构。换句话说，在我可以对它们执行集合操作的意义上，如何测试它们是否相互UNION ALL兼容EXCEPT？

我在 DBA.SE 和其他地方搜索，只能找到关于查找两个表的内容是否不同的问题，（例如检查两个表在 PostgreSQL 中是否具有相同的内容），或者何时知道兼容性（例如比较两个表结构相同，但会员编号不同）。但我有兴趣检查 table structure的兼容性。

我使用的是 PostgreSQL 10.3，但符合标准的方式当然更可取。

postgresql union

5 个回答

  1. Best Answer

    具有示例架构，例如：

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

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

    并假设您不关心列名，您可以使用查询检查联合列中的潜在冲突：

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

    结果将显示违规列在表结构和数据类型中的位置。

    data_type   ordinal_position
integer     3
text    3

    如果您有这个缩短的列表，您可以在 information_schema.columns 字典中查看详细信息，以查看每列的详细信息。

    • 3

  2. 我认为最好的方法就是试试看。

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

    要么它抛出一个错误，要么它没有。

    • 3

  3. 从数据库本身提取有关 SQL 数据库的信息的主要标准方法是INFORMATION_SCHEMA模式，尽管您可能需要自己编写此特定查询。

    在这种情况下information_schema.tables将是关键（有关文档，请参阅https://www.postgresql.org/docs/current/static/infoschema-columns.html）。最简单的版本是表是兼容的，如果表 1 中的每一列都有对应的列与表 2 中的和相同，ordinal_position并且data_type每个中的列数相同（因此表 2 中没有额外内容） .

    为了更高级，您可能必须开始对类型匹配更加模糊：例如，具有不同属性（大小、排序规则）的不同字符串类型（char、varchar、text）可能是兼容的，有些 DB 甚至会让您使用UNION数字和字符串类型通过隐式转换数字[1]等等。

    [1]我不是 postgres 人，所以不能说这里是否是这种情况，但很容易测试。MS SQL Server 试图提供一半的帮助，但会强制使用数字类型，SELECT mixedtype = 1 UNION SELECT mixedtype = '2'但SELECT mixedtype = 'a' UNION SELECT mixedtype = 2不能a强制转换为整数 - 所以为了安全起见，我假设字符串和数字类型永远不兼容。

    • 2

  4. 如果期望字段名称相同，则一种选择是将每个表编写成一个文件，然后使用文件比较工具（例如 WinMerge）并排比较两个脚本。结构上的任何差异都将立即显而易见。

    • 1

  5. 按照接受的答案 - 如果您想知道哪些列名是一个问题，请使用以下内容：

    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'

    如果您只关心名称差异，请SELECT column_name仅使用而不包括其他 2 列。

    • 1

相关问题