PostgreSQL不同环境的表结构检查方案

---

一、通用模型创建-表结构检查表-tzq_check_table_structure_t

CREATE SEQUENCE seq_tzq_check_table_structure_t;
CREATE TABLE tzq_check_table_structure_t (
	check_table_id	INT8	NOT NULL	DEFAULT nextval('seq_tzq_check_table_structure_t'),
	source_system	VARCHAR(100),
	table_name	VARCHAR(64),
	table_comment	VARCHAR(4000),
	column_name	VARCHAR(64),
	column_type	VARCHAR(64),
	column_comment	VARCHAR(4000),
	sys_description	VARCHAR(500),
	PRIMARY KEY (check_table_id)
);
COMMENT ON TABLE  tzq_check_table_structure_t IS '检查表结构表：检查生产环境和测试环境的表结构差异。';
COMMENT ON COLUMN tzq_check_table_structure_t.check_table_id IS '导入信息id：IT主键';
COMMENT ON COLUMN tzq_check_table_structure_t.source_system IS '来源系统：生产、测试环境sit、uat、press、prac2等';
COMMENT ON COLUMN tzq_check_table_structure_t.table_name IS '表名';
COMMENT ON COLUMN tzq_check_table_structure_t.table_comment IS '表注释';
COMMENT ON COLUMN tzq_check_table_structure_t.column_name IS '列名';
COMMENT ON COLUMN tzq_check_table_structure_t.column_type IS '列类型';
COMMENT ON COLUMN tzq_check_table_structure_t.column_comment IS '列注释';
COMMENT ON COLUMN tzq_check_table_structure_t.sys_description IS '系统描述';

二、测试数据插入范本

-- 测试数据插入范本
-- insert into tzq_check_table_structure_t(source_system, table_name, table_comment, column_name, column_type, column_comment)

三、查询表结构结果

-- 查询表结构结果
WITH schemainfo AS (
         SELECT pg_namespace.oid,
            pg_namespace.nspname
           FROM pg_namespace
        ), tbinfo AS (
         SELECT pg_class.oid,
            pg_class.relname,
            (col_description(pg_class.oid, 0))::character varying AS comment,
            pg_class.relkind,
            pg_class.relnamespace
           FROM pg_class
        ), colinfo AS (
         SELECT pg_attribute.attrelid,
            pg_attribute.attname,
            pg_attribute.attnum,
            (format_type(pg_attribute.atttypid, pg_attribute.atttypmod))::character varying AS typelen,
            (col_description(pg_attribute.attrelid, (pg_attribute.attnum)::integer))::character varying AS comment
           FROM pg_attribute
        )
,tab_info_v as (
 SELECT schemainfo.nspname AS schema,
    tbinfo.relname AS table_name,
    tbinfo.comment AS table_comment,
    colinfo.attnum AS column_number,
    colinfo.attname AS column_name,
        CASE
            WHEN ((colinfo.typelen)::text = 'bigint'::text) THEN 'INT8'::character varying
            WHEN ((colinfo.typelen)::text = 'smallint'::text) THEN 'INT2'::character varying
            WHEN ((colinfo.typelen)::text = 'integer'::text) THEN 'INT4'::character varying
            WHEN ((colinfo.typelen)::text = 'text'::text) THEN 'TEXT'::character varying
            WHEN ((colinfo.typelen)::text = 'oid'::text) THEN 'OID'::character varying
            WHEN ("left"((colinfo.typelen)::text, 17) = 'character varying'::text) THEN (replace((colinfo.typelen)::text, 'character varying'::text, 'VARCHAR'::text))::character varying
            WHEN ("left"((colinfo.typelen)::text, 9) = 'character'::text) THEN (replace((colinfo.typelen)::text, 'character'::text, 'CHAR'::text))::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp(6) without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'numeric(38,10)'::text) THEN 'NUMERIC(38,10)'::character varying
            ELSE colinfo.typelen
        END AS column_type,
    colinfo.comment AS column_comment
   FROM tbinfo,
    colinfo,
    schemainfo
  WHERE ((tbinfo.oid = colinfo.attrelid) AND (schemainfo.oid = tbinfo.relnamespace) AND (colinfo.attnum > 0) AND (tbinfo.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) AND (tbinfo.relname !~~ 'pg_%'::text) AND (tbinfo.relname !~~ 'sql_%'::text) AND (colinfo.attname !~~ '%.....pg.dropped%'::text))
  ORDER BY schemainfo.nspname, tbinfo.relname, colinfo.attnum)
select table_name
      ,table_comment
			,column_name
			,column_type
			,column_comment
  from tab_info_v
 where 1=1
   and (table_name like '%\_t' OR table_name like '%\_ti' OR table_name like '%\_tmp')
   and table_name not like 'act\_%'
   -- 查结尾不是数字的表名
   and not table_name ~ '[0-9]{1}$'
	 and table_name not like '%\_bak'
 order by table_name,column_name
;

四、PostgreSQL查表名注释表字段名类型注释

-- insert into tzq_check_table_structure_t(source_system, table_name, table_comment, column_name, column_type, column_comment) 
WITH schemainfo AS (
         SELECT pg_namespace.oid,
            pg_namespace.nspname
           FROM pg_namespace
        ), tbinfo AS (
         SELECT pg_class.oid,
            pg_class.relname,
            (col_description(pg_class.oid, 0))::character varying AS comment,
            pg_class.relkind,
            pg_class.relnamespace
           FROM pg_class
        ), colinfo AS (
         SELECT pg_attribute.attrelid,
            pg_attribute.attname,
            pg_attribute.attnum,
            (format_type(pg_attribute.atttypid, pg_attribute.atttypmod))::character varying AS typelen,
            (col_description(pg_attribute.attrelid, (pg_attribute.attnum)::integer))::character varying AS comment
           FROM pg_attribute
        )
,tab_info_v as (
 SELECT schemainfo.nspname AS schema,
    tbinfo.relname AS table_name,
    tbinfo.comment AS table_comment,
    colinfo.attnum AS column_number,
    colinfo.attname AS column_name,
        CASE
            WHEN ((colinfo.typelen)::text = 'bigint'::text) THEN 'INT8'::character varying
            WHEN ((colinfo.typelen)::text = 'smallint'::text) THEN 'INT2'::character varying
            WHEN ((colinfo.typelen)::text = 'integer'::text) THEN 'INT4'::character varying
            WHEN ((colinfo.typelen)::text = 'text'::text) THEN 'TEXT'::character varying
            WHEN ((colinfo.typelen)::text = 'oid'::text) THEN 'OID'::character varying
            WHEN ("left"((colinfo.typelen)::text, 17) = 'character varying'::text) THEN (replace((colinfo.typelen)::text, 'character varying'::text, 'VARCHAR'::text))::character varying
            WHEN ("left"((colinfo.typelen)::text, 9) = 'character'::text) THEN (replace((colinfo.typelen)::text, 'character'::text, 'CHAR'::text))::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp(6) without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'numeric(38,10)'::text) THEN 'NUMERIC(38,10)'::character varying
            ELSE colinfo.typelen
        END AS column_type,
    colinfo.comment AS column_comment
   FROM tbinfo,
    colinfo,
    schemainfo
  WHERE ((tbinfo.oid = colinfo.attrelid) AND (schemainfo.oid = tbinfo.relnamespace) AND (colinfo.attnum > 0) AND (tbinfo.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) AND (tbinfo.relname !~~ 'pg_%'::text) AND (tbinfo.relname !~~ 'sql_%'::text) AND (colinfo.attname !~~ '%.....pg.dropped%'::text))
  ORDER BY schemainfo.nspname, tbinfo.relname, colinfo.attnum)

-- (source_system, table_name, table_comment, column_name, column_type, column_comment)
,t as (
select 'prac2' as source_system
      ,table_name
      ,table_comment
			,column_name
			,column_type
			,column_comment
  from tab_info_v
 where 1=1
   and (table_name like '%\_t' OR table_name like '%\_ti' OR table_name like '%\_tmp')
   and table_name not like 'act\_%'
   -- 查结尾不是数字的表名
   and not table_name ~ '[0-9]{1}$'
	 and table_name not like '%\_bak'
 order by table_name,column_name
)

五、两个环境表结构数据比对

-- 两个环境表结构数据比对
-- with t as (
-- select table_name, table_comment, column_name, column_type, column_comment
--   from tzq_check_table_structure_t
--  where source_system = 'prac'
-- )
,p as (
select table_name, table_comment, column_name, column_type, column_comment
  from tzq_check_table_structure_t
 where source_system = '生产'
)
select 'prac' as source
     -- ,t.* 
		 ,table_name
      ,table_comment
			,column_name
			,column_type
			,column_comment
 from t 
EXCEPT
select 'prac' as source,p.* from p 
union ALL
select '生产' as source,p.* from p
EXCEPT
select '生产' as source
      -- ,t.* 
		 ,table_name
      ,table_comment
			,column_name
			,column_type
			,column_comment
  from t 
order by table_name,column_name,1
;