database/docs/1.0.2版本需求/03-数据库差异对比表.md

PostgreSQL vs SQL Server 差异对比表

本文档详细列出 PostgreSQL 和 SQL Server 在各个方面的差异，为驱动实现提供参考。

---

一、SQL 语法差异

1.1 标识符引用

特性	PostgreSQL	SQL Server	驱动方法
引用符号	双引号 "name"	方括号 [name]	quoteIdentifier()
转义方式	双引号转义 "my""table"	方括号转义 [my]]table]	驱动内部处理
大小写敏感	默认不敏感,引用后敏感	默认不敏感,取决于排序规则	保持原样
示例	SELECT * FROM "Users"	SELECT * FROM [Users]	驱动生成

实现建议:

// PostgreSQL
quoteIdentifier(id: string): string {
  return `"${id.replace(/"/g, '""')}"`;
}

// SQL Server
quoteIdentifier(id: string): string {
  return `[${id.replace(/\]/g, ']]')}]`;
}

---

1.2 参数占位符

特性	PostgreSQL	SQL Server	驱动方法
占位符格式	$1, $2, $3, ...	@p1, @p2, @p3, ... 或 ?	getParameterPlaceholder()
示例	SELECT * FROM users WHERE id = $1	SELECT * FROM users WHERE id = @p1	驱动生成 SQL
命名参数	不支持	支持 @userId	可选增强

实现建议:

// PostgreSQL
getParameterPlaceholder(index: number): string {
  return `$${index}`;
}

// SQL Server
getParameterPlaceholder(index: number): string {
  return `@p${index}`;
}

---

1.3 分页查询

特性	PostgreSQL	SQL Server	驱动方法
语法	LIMIT n OFFSET m	OFFSET m ROWS FETCH NEXT n ROWS ONLY	buildPaginatedQuery()
位置	查询末尾	查询末尾,需要 ORDER BY	驱动生成
最小版本	所有版本	SQL Server 2012+	检查版本

示例对比:

-- PostgreSQL
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- SQL Server
SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

实现建议:

// PostgreSQL
buildPaginatedQuery(sql, params, limit, offset) {
  return {
    sql: `${sql} LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
    params: [...params, limit, offset]
  };
}

// SQL Server
buildPaginatedQuery(sql, params, limit, offset) {
  return {
    sql: `${sql} OFFSET ${offset} ROWS FETCH NEXT ${limit} ROWS ONLY`,
    params: params  // 不需要额外参数
  };
}

---

1.4 UPSERT (插入或更新)

特性	PostgreSQL	SQL Server	驱动方法
语法	ON CONFLICT ... DO UPDATE	MERGE 语句	buildBulkUpsertStatement()
冲突列指定	ON CONFLICT (id)	ON (target.id = source.id)	驱动生成
返回结果	RETURNING *	OUTPUT INSERTED.*	驱动处理

示例对比:

-- PostgreSQL
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email
RETURNING *;

-- SQL Server
MERGE INTO users AS target
USING (VALUES (1, 'John', 'john@example.com')) AS source (id, name, email)
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
  INSERT (id, name, email) VALUES (source.id, source.name, source.email)
OUTPUT INSERTED.*;

---

1.5 返回插入/更新的行

特性	PostgreSQL	SQL Server	驱动方法
插入返回	RETURNING *	OUTPUT INSERTED.*	驱动生成
更新返回	RETURNING *	OUTPUT INSERTED.*, DELETED.*	驱动生成
删除返回	RETURNING *	OUTPUT DELETED.*	驱动生成
位置	语句末尾	UPDATE/DELETE 之后, SET/WHERE 之前	注意顺序

示例对比:

-- PostgreSQL
INSERT INTO users (name) VALUES ('John') RETURNING id, name;
UPDATE users SET name = 'Jane' WHERE id = 1 RETURNING *;

-- SQL Server
INSERT INTO users (name) OUTPUT INSERTED.id, INSERTED.name VALUES ('John');
UPDATE users SET name = 'Jane' OUTPUT INSERTED.*, DELETED.* WHERE id = 1;

---

1.6 字符串连接

特性	PostgreSQL	SQL Server	驱动方法
运算符	|| 或 CONCAT()	+ 或 CONCAT()	使用 CONCAT() 通用
示例	'Hello' || ' ' || 'World'	'Hello' + ' ' + 'World'	驱动选择

---

1.7 日期时间函数

功能	PostgreSQL	SQL Server	驱动方法
当前时间	NOW(), CURRENT_TIMESTAMP	GETDATE(), CURRENT_TIMESTAMP	使用标准函数
日期部分提取	EXTRACT(YEAR FROM date)	YEAR(date), DATEPART(year, date)	驱动映射
日期运算	date + INTERVAL '1 day'	DATEADD(day, 1, date)	驱动映射
日期格式化	TO_CHAR(date, 'YYYY-MM-DD')	FORMAT(date, 'yyyy-MM-dd')	驱动映射

---

1.8 LIMIT 0 查询 (获取表结构)

特性	PostgreSQL	SQL Server	驱动方法
语法	SELECT * FROM table LIMIT 0	SELECT TOP 0 * FROM table	驱动生成
用途	获取列信息不返回数据	同上	元数据查询

---

二、Schema 和数据库组织

2.1 Schema 概念

特性	PostgreSQL	SQL Server	解决方案
默认 Schema	public	dbo	配置中指定
Schema 路径	search_path 机制	不支持,必须完整限定名	SQL Server 使用 schema.table
设置 Schema	SET search_path TO schema1, schema2	不支持	驱动自动添加 schema 前缀
跨 Schema 查询	自动搜索路径	必须完整限定 schema.table	统一使用完整限定名

实现建议:

// PostgreSQL: 支持 search_path
buildSetSchemaStatement(schemas: string[]): string {
  return `SET search_path TO ${schemas.map(s => this.quoteIdentifier(s)).join(', ')}`;
}

// SQL Server: 不支持,返回空
buildSetSchemaStatement(schemas: string[]): string {
  return '';  // 调用方必须使用完整限定名
}

// 工具层适配
function buildTableReference(driver, table, schema) {
  if (driver.supportsSearchPath()) {
    return driver.quoteIdentifier(table);
  } else {
    return driver.buildQualifiedTableName(table, schema);
  }
}

---

2.2 数据库组织

特性	PostgreSQL	SQL Server	说明
多数据库	支持,每个数据库独立	支持,每个数据库独立	相同
跨数据库查询	需要 dblink 扩展	支持 database.schema.table	SQL Server 更灵活
数据库切换	\c dbname (psql)	USE dbname	连接池每个数据库独立

---

三、数据类型映射

3.1 常用类型对比

通用类型	PostgreSQL	SQL Server	说明
整数 (小)	SMALLINT	SMALLINT	2字节
整数 (中)	INTEGER, INT	INT	4字节
整数 (大)	BIGINT	BIGINT	8字节
自增整数	SERIAL, BIGSERIAL	IDENTITY	不同语法
小数	DECIMAL(p,s), NUMERIC(p,s)	DECIMAL(p,s), NUMERIC(p,s)	相同
浮点数	REAL, DOUBLE PRECISION	REAL, FLOAT	相似
布尔值	BOOLEAN	BIT	SQL Server 用 BIT(0/1)
字符 (定长)	CHAR(n)	CHAR(n), NCHAR(n)	SQL Server 有 Unicode 版本
字符 (变长)	VARCHAR(n)	VARCHAR(n), NVARCHAR(n)	SQL Server 有 Unicode 版本
文本	TEXT	VARCHAR(MAX), NVARCHAR(MAX)	不同类型
二进制	BYTEA	VARBINARY(MAX)	不同类型
日期	DATE	DATE	相同
时间	TIME	TIME	相同
日期时间	TIMESTAMP	DATETIME2	推荐 DATETIME2
时区时间	TIMESTAMPTZ	DATETIMEOFFSET	不同类型
JSON	JSON, JSONB	NVARCHAR(MAX) (2016+: JSON 验证)	PostgreSQL 更强
UUID	UUID	UNIQUEIDENTIFIER	不同类型名
数组	type[]	不支持 (使用 JSON 或关联表)	PostgreSQL 独有
枚举	ENUM	不支持 (使用 CHECK 约束)	PostgreSQL 独有

3.2 类型映射实现

// 驱动提供类型映射
enum GenericDataType {
  INTEGER, STRING, DECIMAL, BOOLEAN, DATE, DATETIME, JSON, UUID, BINARY, TEXT
}

// PostgreSQL
mapToGenericType(pgType: string): GenericDataType {
  const mapping = {
    'integer': GenericDataType.INTEGER,
    'bigint': GenericDataType.INTEGER,
    'varchar': GenericDataType.STRING,
    'text': GenericDataType.TEXT,
    'boolean': GenericDataType.BOOLEAN,
    'timestamp': GenericDataType.DATETIME,
    'json': GenericDataType.JSON,
    'jsonb': GenericDataType.JSON,
    'uuid': GenericDataType.UUID,
    // ...
  };
  return mapping[pgType.toLowerCase()] ?? GenericDataType.UNKNOWN;
}

// SQL Server
mapToGenericType(sqlType: string): GenericDataType {
  const mapping = {
    'int': GenericDataType.INTEGER,
    'bigint': GenericDataType.INTEGER,
    'varchar': GenericDataType.STRING,
    'nvarchar': GenericDataType.STRING,
    'bit': GenericDataType.BOOLEAN,
    'datetime2': GenericDataType.DATETIME,
    'uniqueidentifier': GenericDataType.UUID,
    // ...
  };
  return mapping[sqlType.toLowerCase()] ?? GenericDataType.UNKNOWN;
}

---

四、元数据查询

4.1 系统表/视图对比

元数据类型	PostgreSQL	SQL Server
所有表	pg_tables, information_schema.tables	sys.tables, INFORMATION_SCHEMA.TABLES
所有列	information_schema.columns	sys.columns, INFORMATION_SCHEMA.COLUMNS
所有视图	pg_views, information_schema.views	sys.views, INFORMATION_SCHEMA.VIEWS
所有索引	pg_indexes	sys.indexes
所有约束	pg_constraint, information_schema.table_constraints	sys.check_constraints, INFORMATION_SCHEMA.TABLE_CONSTRAINTS
外键	information_schema.referential_constraints	sys.foreign_keys, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
主键	pg_constraint	sys.key_constraints
存储过程	pg_proc, information_schema.routines	sys.procedures, INFORMATION_SCHEMA.ROUTINES
触发器	pg_trigger, information_schema.triggers	sys.triggers, INFORMATION_SCHEMA.TRIGGERS
Schema	pg_namespace, information_schema.schemata	sys.schemas, INFORMATION_SCHEMA.SCHEMATA
数据库	pg_database	sys.databases

4.2 查询示例

列出所有表

-- PostgreSQL
SELECT tablename, schemaname
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

-- SQL Server
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

描述表结构

-- PostgreSQL
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users' AND table_schema = 'public';

-- SQL Server
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = 'dbo';

列出索引

-- PostgreSQL
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'users';

-- SQL Server
SELECT i.name, ic.index_column_id, c.name as column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('dbo.users');

---

五、事务和并发

5.1 事务语法

特性	PostgreSQL	SQL Server	兼容性
开始事务	BEGIN, START TRANSACTION	BEGIN TRANSACTION, BEGIN TRAN	都支持 BEGIN
提交	COMMIT	COMMIT	✅ 相同
回滚	ROLLBACK	ROLLBACK	✅ 相同
保存点	SAVEPOINT name	SAVE TRANSACTION name	语法稍异
回滚到保存点	ROLLBACK TO SAVEPOINT name	ROLLBACK TRANSACTION name	语法稍异

实现建议: 都支持 SAVEPOINT,驱动统一使用标准 SQL 语法。

5.2 隔离级别

隔离级别	PostgreSQL	SQL Server	兼容性
READ UNCOMMITTED	✅ 支持	✅ 支持	✅ 相同
READ COMMITTED	✅ 默认	✅ 默认	✅ 相同
REPEATABLE READ	✅ 支持	✅ 支持	✅ 相同
SERIALIZABLE	✅ 支持	✅ 支持	⚠️ 实现不同

设置隔离级别:

-- PostgreSQL
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

5.3 锁机制

特性	PostgreSQL	SQL Server	说明
行级锁	支持	支持	都支持
表级锁	LOCK TABLE	LOCK TABLE, WITH (TABLOCKX)	语法不同
显式锁提示	较少使用	WITH (NOLOCK), WITH (ROWLOCK)	SQL Server 更丰富
死锁检测	自动	自动	都自动检测

---

六、函数和存储过程

6.1 函数语法

特性	PostgreSQL	SQL Server	说明
创建函数	CREATE FUNCTION	CREATE FUNCTION	语法大不同
函数语言	SQL, PL/pgSQL, Python, etc.	T-SQL	语言不同
返回类型	RETURNS type	RETURNS type	相似
返回表	RETURNS TABLE	RETURNS TABLE	都支持

示例:

-- PostgreSQL
CREATE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- SQL Server
CREATE FUNCTION get_user_count()
RETURNS INT
AS
BEGIN
  RETURN (SELECT COUNT(*) FROM users);
END;

6.2 存储过程

特性	PostgreSQL	SQL Server	说明
创建过程	CREATE PROCEDURE (PG 11+)	CREATE PROCEDURE	PG 11+ 支持
调用	CALL proc_name()	EXEC proc_name	语法不同
参数	IN, OUT, INOUT	@param type [OUTPUT]	语法不同

---

七、查询分析和诊断

7.1 执行计划

特性	PostgreSQL	SQL Server	驱动实现
查看计划	EXPLAIN	需要 Management Studio 或 SET SHOWPLAN_*	不同方式
格式化输出	EXPLAIN (FORMAT JSON)	XML 格式执行计划	驱动解析
实际执行	EXPLAIN ANALYZE	SET STATISTICS IO ON/TIME ON	不同实现

PostgreSQL:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE id = 1;

SQL Server:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM users WHERE id = 1;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

7.2 活跃连接监控

PostgreSQL:

SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state = 'active';

SQL Server:

SELECT session_id, login_name, program_name, status, last_request_start_time
FROM sys.dm_exec_sessions
WHERE status = 'running';

7.3 锁监控

PostgreSQL:

SELECT locktype, database, relation::regclass, pid, mode, granted
FROM pg_locks
WHERE NOT granted;

SQL Server:

SELECT
  request_session_id,
  resource_type,
  resource_database_id,
  request_mode,
  request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

---

八、特有功能对比

8.1 PostgreSQL 独有功能

功能	说明	SQL Server 替代方案
JSONB	二进制 JSON,高性能	JSON 字符串 (2016+)
数组类型	原生数组支持	JSON 或关联表
ENUM 类型	枚举类型	CHECK 约束
继承表	表继承	视图或关联
范围类型	INT4RANGE, TSRANGE	自定义类型
全文搜索	内置 tsvector	Full-Text Search
并行查询	内置并行查询	自动并行 (Enterprise)
VACUUM	清理死元组	自动管理
物化视图	CREATE MATERIALIZED VIEW	索引视图 (有限制)
扩展系统	CREATE EXTENSION	CLR 或第三方
LISTEN/NOTIFY	异步通知	Service Broker

8.2 SQL Server 独有功能

功能	说明	PostgreSQL 替代方案
列存储索引	列式存储	cstore_fdw 扩展
内存表	In-Memory OLTP	无直接替代
分区表自动管理	更强大的分区	声明式分区 (PG 10+)
Always Encrypted	列级加密	pgcrypto 扩展
图数据库功能	MATCH 语句	Apache AGE 扩展
时态表	系统版本控制表	自定义触发器
行级安全	内置 RLS	PG 也有 RLS

---

九、驱动实现优先级

高优先级 (P0) - 必须实现

✅ 连接管理 ✅ 基本查询 (SELECT, INSERT, UPDATE, DELETE) ✅ 事务管理 (BEGIN, COMMIT, ROLLBACK) ✅ 参数化查询 ✅ 分页查询 ✅ 基础元数据 (listTables, describeTable) ✅ 标识符引用

中优先级 (P1) - 应该实现

⚠️ UPSERT 操作 ⚠️ 批量插入 ⚠️ 完整元数据浏览 (索引、约束、视图) ⚠️ 查询执行计划 ⚠️ 保存点支持

低优先级 (P2) - 可以延后

⭕ 高级诊断功能 ⭕ 慢查询分析 ⭕ 锁监控 ⭕ 复制状态 ⭕ 数据库维护命令

---

十、测试用例建议

测试覆盖矩阵

功能	PostgreSQL 测试	SQL Server 测试	兼容性测试
连接创建	✅	✅	✅
SELECT 查询	✅	✅	✅
INSERT	✅	✅	✅
UPDATE	✅	✅	✅
DELETE	✅	✅	✅
事务	✅	✅	✅
分页	✅	✅	✅
UPSERT	✅	✅	✅
批量插入	✅	✅	✅
Schema 切换	✅	✅	⚠️ 行为不同
元数据查询	✅	✅	✅

---

十一、迁移注意事项

从 PostgreSQL 迁移到 SQL Server

1. Schema 引用: 所有查询必须使用完整限定名 schema.table
2. 数据类型: SERIAL → IDENTITY, BOOLEAN → BIT
3. JSON: JSONB → NVARCHAR(MAX) 或 JSON (2016+)
4. 数组: 需要改为 JSON 或关联表
5. UPSERT: ON CONFLICT → MERGE
6. LIMIT: LIMIT/OFFSET → OFFSET/FETCH

从 SQL Server 迁移到 PostgreSQL

1. 标识符: [name] → "name"
2. TOP: SELECT TOP 10 → SELECT ... LIMIT 10
3. IDENTITY: IDENTITY(1,1) → SERIAL 或 GENERATED ALWAYS
4. MERGE: 改为 INSERT ... ON CONFLICT
5. OUTPUT: OUTPUT INSERTED.* → RETURNING *

---

文档维护:

• 负责人: [待指定]
• 最后更新: 2024-12-27