openGauss使用指南与SQL转换注意事项

openGauss 使用指南与SQL转换注意事项

基本说明

openGauss数据库内核基于PostgreSQL(pgsql)，因此可以将SQL Server语句转换为pgsql语句。可以使用AI工具辅助转换，但需注意以下关键差异点。

数据类型转换注意事项

字符串类型处理

• nvarchar转换：当字段包含中文时，nvarchar(N)需转换为varchar(2N)
  • 示例：nvarchar(32) → varchar(64)

特殊类型映射

SQL Server类型	PostgreSQL对应类型
varchar(max)	text
nvarchar(max)	text
IDENTITY	SERIAL
datetime	TIMESTAMP
float	float8
tinyint	int2
int	int4
varbinary(max)	BYTEA

说明

在 PostgreSQL 中，TIMESTAMP(6) 表示一个时间戳，其中 6 指定了秒的小数部分（微秒）的精度位数。

TIMESTAMP(6) 的详细说明：

• TIMESTAMP 默认精度为 TIMESTAMP(6)，即微秒级（6位小数）。
• (6) 表示秒的小数部分保留 6位（即精确到 微秒，1秒 = 1,000,000 微秒）。
• 如果指定 TIMESTAMP(0)，则完全舍弃小数部分，只保留到秒。Date类型会自动转成timestamp(0)

示例对比：

数据类型	示例输出	精度级别
TIMESTAMP(0)	2025-04-11 12:34:56	秒（无小数）
TIMESTAMP(3)	2025-04-11 12:34:56.789	毫秒（3位小数）
TIMESTAMP(6)	2025-04-11 12:34:56.789123	微秒（6位小数）

在 ErrorLog 表中使用 TIMESTAMP(6)

如果你想在 PostgreSQL 中让 RequestTime 和 FinishTime 精确到微秒（6位小数），可以这样定义：

CREATE TABLE ErrorLog (Id                   SERIAL PRIMARY KEY,IPAddress            VARCHAR(255),HttpMethod           VARCHAR(255),Account              VARCHAR(255),Url                  VARCHAR(255),RequestData          VARCHAR(255),ResultData           TEXT,RequestTime          TIMESTAMP(6) NOT NULL,  -- 微秒级时间戳FinishTime           TIMESTAMP(6) NOT NULL,  -- 微秒级时间戳ErrorMessage         TEXT,LogName              VARCHAR(255)
);

其他相关时间类型

• TIMESTAMPTZ(6)：带时区的微秒级时间戳。
• DATE：仅存储日期（无时间）。
• TIME(6)：仅存储时间（可带微秒）。

如果你不需要微秒级精度，可以直接用 TIMESTAMP（默认 TIMESTAMP(6)）或降低精度，如 TIMESTAMP(3)（毫秒）。

常用操作示例

1. 修改字段名

DO $$
BEGIN-- 安全修改字段名（推荐方式）IF NOT EXISTS (SELECT 1 FROM information_schema.columnsWHERE table_name = 'customformfield'AND column_name = 'customformgroup') AND EXISTS (SELECT 1 FROM information_schema.columnsWHERE table_name = 'customformfield'AND column_name = 'group') THENALTER TABLE customformfield RENAME COLUMN "Group" TO CustomFormGroup;END IF;
END $$;-- 或使用异常处理方式
DO $$
BEGINALTER TABLE customformfield RENAME COLUMN "Group" TO CustomFormGroup;
EXCEPTION WHEN undefined_column THEN RAISE NOTICE '原列"Group"不存在';WHEN duplicate_column THENRAISE NOTICE '目标列"CustomFormGroup"已存在';
END $$;

2. 添加字段

DO $$
BEGIN-- 检查后添加（兼容旧版本）IF NOT EXISTS (SELECT 1 FROM information_schema.columnsWHERE table_name = 'currentdialysisprogram'AND column_name = 'addonmode') THENALTER TABLE CurrentDialysisProgram ADD COLUMN AddOnMode smallint;END IF;
END $$;-- 或使用异常处理方式（PostgreSQL 9.6+推荐）
DO $$
BEGINALTER TABLE CurrentDialysisProgram ADD COLUMN AddOnMode smallint;
EXCEPTION WHEN duplicate_column THEN RAISE NOTICE '列"AddOnMode"已存在';
END $$;

3. 删除字段（谨慎使用）

DO $$
BEGINIF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'observerecord' AND column_name = 'measureposition1') THENALTER TABLE ObserveRecord DROP COLUMN MeasurePosition1;END IF;
END $$;

4. 创建表

DO $$
BEGINIF NOT EXISTS (SELECT 1 FROM pg_tablesWHERE schemaname = 'public'AND tablename = 'requestlog') THENCREATE TABLE requestlog (id SERIAL PRIMARY KEY,ip_address VARCHAR(20),http_method VARCHAR(10),account VARCHAR(200),url VARCHAR(200),request_data TEXT,result_data TEXT,request_time TIMESTAMP,finish_time TIMESTAMP,error_message TEXT,log_name VARCHAR(100));END IF;
END $$;

查询语法差异

分页查询

-- SQL Server
SELECT TOP(1) * FROM Patient-- PostgreSQL
SELECT * FROM Patient LIMIT 1

行号处理

-- SQL Server
SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY PatientInfoType ORDER BY EditTime DESC) rownum, *FROM PatientInfoHistoryRecordsWHERE DataState = 1 AND PatientId = @PatientId
) p WHERE rownum = 1-- PostgreSQL
SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY PatientInfoType ORDER BY EditTime DESC) row_num, *FROM PatientInfoHistoryRecordsWHERE DataState = 1 AND PatientId = :PatientId
) p WHERE row_num = 1

函数转换

日期函数

-- SQL Server
DATEPART(YEAR, a.FounderDate) = @Year-- PostgreSQL
date_part('YEAR', a.FounderDate) = :Year

UUID生成

-- SQL Server
replace(newid(),'-','')-- PostgreSQL
REPLACE(md5(random()::text || clock_timestamp()::text)::uuid::text, '-', '')

默认值处理

SQL Server	PostgreSQL
getdate()	CURRENT_DATE
default (0)	default 0 (数值) default ‘0’ (字符串)

最佳实践

1. 表名、列名尽量使用小写
2. 关键字需用双引号括起
3. 参数使用:前缀而非@
4. 重要操作前建议先检查对象是否存在
5. 删除操作需特别谨慎

跨库查询

由于pgsql不支持跨库查询，所以在写sql时候注意不要使用两个库的表来连接查询（例如:机器库和业务库的表）。
如果是基础数据（例如字典）可以迁移至机器库，单库查询，或者是使用两个不同sql查询,然后内存组装数据。

  //查询业务库字典表string sql = "select * from SystemDictionary where id='xxxx'";var dt = _idal.Query(sql);var list1= GetTupleByList<T>(dt);//查询机器库string sql2 = "select *from MachineData_HardWareInfo where ...";var dt2 = _idal.QueryMachineData(sql2);var list2 = GetTupleByList<T>(dt2);

PostgreSQL 关键字分类及作为标识符的使用规则

以下是 PostgreSQL 关键字分类及其作为列名/表名使用规则的详细表格：

分类代码	类别名称	能否作为列名	能否作为表名	是否需要引号	示例关键字	使用示例
'C'	保留关键字 (Reserved)	❌ 不能直接使用	❌ 不能直接使用	✅ 必须加双引号	SELECT, TABLE, USER	CREATE TABLE "user" ("select" int)
'U'	非保留关键字 (Unreserved)	✅ 可以直接使用	✅ 可以直接使用	❌ 不需要引号	COMMENT, ENCRYPT, VERSION	CREATE TABLE test (comment text)
'T'	类型/函数名关键字	✅ 可以直接使用	✅ 可以直接使用	❌ 不需要引号	DATE, TIME, TIMESTAMP	CREATE TABLE log (time timestamp)
'R'	有限保留关键字	⚠️ 部分上下文可用	⚠️ 部分上下文可用	视情况而定	LOCATION, PARTITION, ROWS	CREATE TABLE test (location text)

使用建议

1. 最佳实践：即使是非保留关键字('U')，也建议避免使用与关键字相同或相似的名称作为标识符

2. 引号规则：

   • 双引号用于标识符："user"
   • 单引号用于字符串值：'admin'

3. 特殊情况：

   -- 有限保留关键字在特定上下文可能需要引号
   CREATE TABLE test ("rows" int,  -- 有限保留关键字需要引号data text    -- 非关键字不需要引号
   );
   

4. 查询关键字分类：

   -- 查询特定单词的分类
   SELECT * FROM pg_get_keywords() WHERE word = '分类代码';
   

当不确定某个词是否是关键字时，使用引号是最安全的做法。