全量比较-pg侧前置内容
1. 创建测试表SQL
-- PostgreSQL 完整数据类型测试表
-- 用于测试跨数据库整行 hash 一致性
DROP TABLE IF EXISTS test_all_types CASCADE;
CREATE TABLE test_all_types (
-- ============ 主键与索引 ============
id BIGSERIAL PRIMARY KEY,
unique_key VARCHAR(100) UNIQUE,
-- ============ 数值类型 ============
col_smallint SMALLINT,
col_integer INTEGER,
col_bigint BIGINT,
col_decimal DECIMAL(20, 10),
col_numeric NUMERIC(30, 15),
col_real REAL,
col_double DOUBLE PRECISION,
col_smallserial_col SMALLSERIAL,
col_serial_col SERIAL,
-- ============ 货币类型 ============
col_money MONEY,
-- ============ 字符类型 ============
col_varchar VARCHAR(50),
col_varchar_empty VARCHAR(10),
col_char CHAR(10),
col_char_padded CHAR(20),
col_text TEXT,
col_text_long TEXT,
-- ============ 二进制类型 ============
col_bytea BYTEA,
-- ============ 日期时间类型 ============
col_date DATE,
col_time_without_zone TIME,
col_time_without_zone_3 TIME(3),
col_time_without_zone_6 TIME(6),
col_time_with_zone TIME WITH TIME ZONE,
col_time_with_zone_3 TIME(3) WITH TIME ZONE,
col_timestamp_without TIMESTAMP,
col_timestamp_3 TIMESTAMP(3),
col_timestamp_6 TIMESTAMP(6),
col_timestamp_with_zone TIMESTAMP WITH TIME ZONE,
col_timestamp_with_zone_3 TIMESTAMP(3) WITH TIME ZONE,
col_timestamp_with_zone_6 TIMESTAMP(6) WITH TIME ZONE,
col_interval interval,
-- ============ 布尔类型 ============
col_boolean BOOLEAN,
-- ============ 位串类型 ============
col_bit_varying BIT VARYING(20),
col_bit BIT(8),
-- ============ 网络类型 ============
col_cidr CIDR,
col_inet INET,
col_macaddr MACADDR,
-- ============ UUID ============
col_uuid UUID,
-- ============ JSON 类型 ============
col_json JSON,
col_jsonb JSONB,
-- ============ 数组类型 ============
col_int_array INTEGER[],
col_text_array TEXT[],
col_varchar_array VARCHAR(50)[],
-- ============ 几何类型 ============
col_point POINT,
col_line LINE,
col_lseg LSEG,
col_box BOX,
col_path PATH,
col_polygon POLYGON,
col_circle CIRCLE,
-- ============ 范围类型 ============
col_int4range INT4RANGE,
col_int8range INT8RANGE,
col_numrange NUMRANGE,
col_tsrange TSRANGE,
col_tstzrange TSTZRANGE,
col_daterange DATERANGE,
-- ============ XML ============
col_xml XML,
-- ============ 全文搜索 ============
col_tsvector TSVECTOR,
-- ============ 允许 NULL 的列 ============
col_null_integer INTEGER,
col_null_varchar VARCHAR,
col_null_date DATE
);
CREATE INDEX idx_unique_key ON test_all_types(unique_key);
CREATE INDEX idx_timestamp ON test_all_types(col_timestamp_with_zone);
CREATE INDEX idx_varchar ON test_all_types(col_varchar);
CREATE INDEX idx_integer ON test_all_types(col_integer);
CREATE INDEX idx_tsvector_idx ON test_all_types USING GIN (col_tsvector);
COMMENT ON TABLE test_all_types IS 'PostgreSQL 全数据类型测试表 - 用于跨数据库整行 hash 一致性验证';
-- ============ 插入测试数据 ============
TRUNCATE TABLE test_all_types RESTART IDENTITY;
INSERT INTO test_all_types (
unique_key,
col_smallint, col_integer, col_bigint,
col_decimal, col_numeric, col_real, col_double,
col_smallserial_col, col_serial_col,
col_money,
col_varchar, col_varchar_empty, col_char, col_char_padded,
col_text, col_text_long,
col_bytea,
col_date,
col_time_without_zone, col_time_without_zone_3, col_time_without_zone_6,
col_time_with_zone, col_time_with_zone_3,
col_timestamp_without, col_timestamp_3, col_timestamp_6,
col_timestamp_with_zone, col_timestamp_with_zone_3, col_timestamp_with_zone_6,
col_interval,
col_boolean,
col_bit_varying, col_bit,
col_cidr, col_inet, col_macaddr,
col_uuid,
col_json, col_jsonb,
col_int_array, col_text_array, col_varchar_array,
col_point, col_line, col_lseg, col_box, col_path, col_polygon, col_circle,
col_int4range, col_int8range, col_numrange,
col_tsrange, col_tstzrange, col_daterange,
col_xml,
col_tsvector,
col_null_integer, col_null_varchar, col_null_date
) VALUES (
-- ============ 第一条：常规值 + 边界值 ============
'ROW_001_BASIC',
-32768, 0, 9223372036854775807,
1234567890.1234567890::DECIMAL(20, 10),
-999999999999.999999999999999::NUMERIC(30, 15),
3.1415927::REAL, 3.141592653589793::DOUBLE PRECISION,
DEFAULT, DEFAULT,
'$1,234.56'::MONEY,
'Hello World', '', 'A', '文本填充测试' || LPAD('', 13, ' '),
'这是很长的 TEXT 内容，
可以包含多行文本。
支持任意长度的字符串。',
REPEAT('A', 1000),
E'\\xDEADBEEF'::BYTEA,
'2024-12-31'::DATE,
'13:45:59'::TIME, '13:45:59.123'::TIME(3), '13:45:59.123456'::TIME(6),
'13:45:59+08:00'::TIME WITH TIME ZONE, '13:45:59.123+08:00'::TIME(3) WITH TIME ZONE,
'2024-01-01 00:00:00'::TIMESTAMP,
'2024-06-15 12:30:45.123'::TIMESTAMP(3),
'2024-06-15 12:30:45.123456'::TIMESTAMP(6),
'2024-06-15 20:30:45+08:00'::TIMESTAMP WITH TIME ZONE,
'2024-06-15 20:30:45.123+08:00'::TIMESTAMP(3) WITH TIME ZONE,
'2024-06-15 20:30:45.123456+08:00'::TIMESTAMP(6) WITH TIME ZONE,
'2 years 3 months 15 days 6 hours 30 minutes 45 seconds'::INTERVAL,
true,
B'10101', B'11110000',
'192.168.0.0/24'::CIDR, '10.0.0.1'::INET, '08:00:2b:01:02:03'::MACADDR,
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID,
'{"name": "张三", "age": 30, "score": 95.5, "active": true, "tags": ["测试", "示例"], "nested": {"key": "value"}}'::JSON,
'{"name": "李四", "age": 25, "items": [1, 2, 3], "float": 3.14159}'::JSONB,
ARRAY[1, 2, 3, 4, 5],
ARRAY['apple', 'banana', '中文水果'],
ARRAY['high', 'medium', 'low']::VARCHAR(50)[],
POINT(10.5, 20.5), LINE '{1, 2, 3}', LSEG '[(0,0), (10,10)]',
BOX '(5,5), (0,0)', PATH '[(0,0), (1,1), (2,0)]',
POLYGON '((0,0), (1,0), (1,1), (0,1))', CIRCLE '<(5,5), 3>',
INT4RANGE '[1, 100]', INT8RANGE '[1000, 9999999999999]',
NUMRANGE '[0.1, 100.5]',
TSRANGE '[2024-01-01 00:00:00, 2024-12-31 23:59:59]',
TSTZRANGE '[2024-01-01 00:00:00+08:00, 2024-12-31 23:59:59+08:00]',
DATERANGE '[2024-01-01, 2024-12-31]',
'<root><item id="1">第一个元素</item><item id="2">第二个元素</item></root>'::XML,
to_tsvector('english', 'The quick brown fox jumps over the lazy dog 中文全文搜索测试'),
NULL, NULL, NULL
),
-- ============ 第二条：边界值 + NULL + 极值 ============
(
'ROW_002_EDGE_CASES',
32767, -2147483648, -9223372036854775808,
-0.0000000001::DECIMAL(20, 10), 0.000000000000001::NUMERIC(30, 15),
-0.0::REAL, -1.7976931348623157E+308::DOUBLE PRECISION,
DEFAULT, DEFAULT,
'$ -1,000.00'::MONEY,
'Unicode: 中文 日本語 한국어', ' ', '中', LPAD('', 20, 'X'),
'', E'\t\n\r\\\'\"',
E'\\x00010203040506070809'::BYTEA,
'1970-01-01'::DATE,
'00:00:00'::TIME, '00:00:00.000'::TIME(3), '00:00:00.000000'::TIME(6),
'00:00:00+00:00'::TIME WITH TIME ZONE, '23:59:59.999+00:00'::TIME(3) WITH TIME ZONE,
'1970-01-01 00:00:00'::TIMESTAMP,
'1970-01-01 00:00:00.000'::TIMESTAMP(3),
'1970-01-01 00:00:00.000000'::TIMESTAMP(6),
'1970-01-01 00:00:00+00:00'::TIMESTAMP WITH TIME ZONE,
'2038-01-19 03:14:07.999+00:00'::TIMESTAMP(3) WITH TIME ZONE,
'2038-01-19 03:14:07.999999+00:00'::TIMESTAMP(6) WITH TIME ZONE,
'-1 year -2 months -3 days'::INTERVAL,
false,
B'00000', B'00000000',
'2001:db8::/32'::CIDR, '::1'::INET, '00:00:00:00:00:00'::MACADDR,
'00000000-0000-0000-0000-000000000000'::UUID,
'null'::JSON, '[]'::JSONB,
ARRAY[]::INTEGER[], ARRAY[]::TEXT[], ARRAY[NULL]::VARCHAR(50)[],
POINT(0, 0), NULL, NULL, NULL, NULL, NULL, NULL,
INT4RANGE '[0, 0]', NULL, NULL, NULL, NULL, NULL,
NULL,
to_tsvector('english', ''),
NULL, NULL, NULL
),
-- ============ 第三条：全值填充 + BC 日期 + 最大 UUID ============
(
'ROW_003_FULL_VALUES',
1, 1, 1,
1.0::DECIMAL(20, 10), 1.0::NUMERIC(30, 15),
1.0::REAL, 1.0::DOUBLE PRECISION,
DEFAULT, DEFAULT,
'$0.01'::MONEY,
'1', '1', '1', '1 ',
'1', '1',
E'\\x01'::BYTEA,
'0001-01-01 BC'::DATE,
'00:00:00'::TIME, '00:00:00.001'::TIME(3), '00:00:00.000001'::TIME(6),
'00:00:00+00:00'::TIME WITH TIME ZONE, '00:00:00.001+00:00'::TIME(3) WITH TIME ZONE,
'0001-01-01 00:00:00 BC'::TIMESTAMP,
'0001-01-01 00:00:00.001 BC'::TIMESTAMP(3),
'0001-01-01 00:00:00.000001 BC'::TIMESTAMP(6),
'0001-01-01 00:00:00+00:00 BC'::TIMESTAMP WITH TIME ZONE,
'0001-01-01 00:00:00.001+00:00 BC'::TIMESTAMP(3) WITH TIME ZONE,
'0001-01-01 00:00:00.000001+00:00 BC'::TIMESTAMP(6) WITH TIME ZONE,
'1 second'::INTERVAL,
NULL,
B'1', B'00000001',
'0.0.0.0/0'::CIDR, '0.0.0.0'::INET, 'ff:ff:ff:ff:ff:ff'::MACADDR,
'ffffffff-ffff-ffff-ffff-ffffffffffff'::UUID,
'0'::JSON, '0'::JSONB,
ARRAY[0], ARRAY['0'], ARRAY['0']::VARCHAR(50)[],
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
INT4RANGE '[0, 1)', NULL, NULL, NULL, NULL, NULL,
'<element/>'::XML,
to_tsvector('simple', 'test'),
100, '有值的 VARCHAR', '2024-01-01'
);
SELECT '数据插入完成' AS status;
SELECT COUNT(*) AS total_rows FROM test_all_types;
SELECT id, unique_key, col_varchar, col_integer, col_boolean FROM test_all_types;2.hash查询语句
SELECT
id,
md5(
COALESCE(col_smallint::text, '') ||
COALESCE(col_integer::text, '') ||
COALESCE(col_bigint::text, '') ||
COALESCE(ROUND(col_decimal::numeric, 10)::text, '') ||
COALESCE(ROUND(col_numeric::numeric, 15)::text, '') ||
COALESCE(ROUND(col_real::numeric, 7)::text, '') ||
COALESCE(ROUND(col_double::numeric, 15)::text, '') ||
COALESCE(col_money::text, '') ||
COALESCE(col_varchar, '') ||
COALESCE(col_text, '') ||
COALESCE(TRIM(col_char)::text, '') ||
COALESCE(encode(col_bytea, 'hex'), '') ||
COALESCE(col_boolean::text, '') ||
COALESCE(col_uuid::text, '') ||
COALESCE((col_timestamp_with_zone AT TIME ZONE 'UTC')::text, '') ||
COALESCE(col_jsonb::text, '') ||
COALESCE(array_to_string(col_int_array, ','), '') ||
COALESCE(col_int4range::text, '')
) AS row_hash
FROM test_all_types
ORDER BY id;3.pgsql查询字段名称和数据类型(忽略精度)
SELECT
table_schema,
column_name,
data_type
FROM
information_schema.columns
WHERE
table_schema = 'test' AND
table_name = 'test_all_types'
ORDER BY
table_schema, ordinal_position;4.pgsql查询字段名称和数据类型（不忽略精度）（查询结果需要注意区分，例如时间戳）
SELECT
a.attname AS 字段名,
format_type(a.atttypid, a.atttypmod) AS 完整类型
FROM
pg_attribute a
WHERE
a.attrelid = 'test.test_all_types'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY
a.attnum;5.gaussDB查询字段名称和数据类型（未验证）
-- 基础查询
SELECT
column_name,
data_type
FROM
information_schema.columns
WHERE
table_schema = 'public' -- 或具体 schema 名
AND table_name = 'test_all_types'
ORDER BY
ordinal_position;
-- 带精度信息的完整类型查询
SELECT
column_name AS 字段名,
CASE
WHEN data_type IN ('character varying', 'character')
THEN data_type || '(' || character_maximum_length || ')'
WHEN data_type = 'numeric'
THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
WHEN data_type IN ('timestamp without time zone', 'timestamp with time zone')
THEN data_type || COALESCE('(' || datetime_precision || ')', '')
ELSE data_type
END AS 完整类型
FROM
information_schema.columns
WHERE
table_schema = 'public'
AND table_name = 'test_all_types'
ORDER BY
ordinal_position;
-- 使用 GaussDB 系统表查询
SELECT
a.attname AS 字段名,
format_type(a.atttypid, a.atttypmod) AS 数据类型
FROM
pg_attribute a
WHERE
a.attrelid = 'public.test_all_types'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY
a.attnum;6.gaussDB生成整行hash(未验证)
-- 在 GaussDB 中生成 hash 记录
CREATE TABLE hash_records AS
SELECT
id,
unique_key,
md5(
COALESCE(col_smallint::text, '') ||
COALESCE(col_integer::text, '') ||
COALESCE(col_bigint::text, '') ||
COALESCE(col_decimal::text, '') ||
COALESCE(col_numeric::text, '') ||
COALESCE(col_real::text, '') ||
COALESCE(col_double::text, '') ||
COALESCE(col_money::text, '') ||
COALESCE(col_varchar, '') ||
COALESCE(col_varchar_empty, '') ||
COALESCE(TRIM(col_char)::text, '') ||
COALESCE(col_text, '') ||
COALESCE(encode(col_bytea, 'hex'), '') ||
COALESCE(col_date::text, '') ||
COALESCE(col_time_without_zone::text, '') ||
COALESCE(col_time_without_zone_3::text, '') ||
COALESCE(col_time_without_zone_6::text, '') ||
COALESCE(col_time_with_zone::text, '') ||
COALESCE(col_time_with_zone_3::text, '') ||
COALESCE(col_timestamp_without::text, '') ||
COALESCE(col_timestamp_3::text, '') ||
COALESCE(col_timestamp_6::text, '') ||
COALESCE(col_timestamp_with_zone::text, '') ||
COALESCE(col_timestamp_with_zone_3::text, '') ||
COALESCE(col_timestamp_with_zone_6::text, '') ||
COALESCE(col_interval::text, '') ||
COALESCE(col_boolean::text, '') ||
COALESCE(col_bit_varying::text, '') ||
COALESCE(col_bit::text, '') ||
COALESCE(col_cidr::text, '') ||
COALESCE(col_inet::text, '') ||
COALESCE(col_macaddr::text, '') ||
COALESCE(col_uuid::text, '') ||
COALESCE(col_json::text, '') ||
COALESCE(col_jsonb::text, '') ||
COALESCE(array_to_string(col_int_array, ','), '') ||
COALESCE(array_to_string(col_text_array, ','), '') ||
COALESCE(array_to_string(col_varchar_array, ','), '') ||
COALESCE(col_point::text, '') ||
COALESCE(col_line::text, '') ||
COALESCE(col_lseg::text, '') ||
COALESCE(col_box::text, '') ||
COALESCE(col_path::text, '') ||
COALESCE(col_polygon::text, '') ||
COALESCE(col_circle::text, '') ||
COALESCE(col_int4range::text, '') ||
COALESCE(col_int8range::text, '') ||
COALESCE(col_numrange::text, '') ||
COALESCE(col_tsrange::text, '') ||
COALESCE(col_tstzrange::text, '') ||
COALESCE(col_daterange::text, '') ||
COALESCE(col_xml::text, '') ||
COALESCE(col_tsvector::text, '') ||
COALESCE(col_null_integer::text, '') ||
COALESCE(col_null_varchar, '') ||
COALESCE(col_null_date::text, '')
) AS gaussdb_hash
FROM test_all_types;
SELECT * FROM hash_records;
浙公网安备 33010602011771号