sqlserver转PG经验总结及PG的一些特性

### SQL Server 转 PostgreSQL 经验总结及 PostgreSQL 特性 #### 一、SQL Server 到 PostgreSQL 的数据类型映射 在进行 SQL Server 数据库迁移至 PostgreSQL 的过程中，一个重要的步骤是确保数据类型的正确映射。以下是 SQL Server 与 PostgreSQL 常见的数据类型对照表： - `char(n)` 映射到 `char(n)` - `varchar(n)` 映射到 `text`（PostgreSQL 的 `text` 类型没有长度限制） - `int` 映射到 `int` - `bigint` 映射到 `bigint` - `datetime` 映射到 `timestamp` - `money` 映射到 `numeric(19,4)` - `date` 映射到 `date` - `double` 映射到 `double precision` - `float` 映射到 `float` - `numeric` 映射到 `numeric`（通常用于存储精确的数值，如经纬度） - `image` 映射到 `bytea`（用于存储二进制大对象） - `bool` 映射到 `bool` - `uniqueidentifier` 映射到 `uuid` - `varbinary` 映射到 `bytea` - `timestamp` 映射到 `timestamp with time zone` 对于日期和时间操作，可以使用 PostgreSQL 内置函数来实现 SQL Server 中的功能，例如： - `getdate()` 可以通过 `now()` 替换 - `dateadd()` 可以通过 `now() + interval` 来实现，注意如果是以参数形式传入，则需要使用类似 `now() + make_interval(days => 1 - i)` 的方式 - `datediff()` 可以通过 `date_part('day', endtime - starttime)` 实现 #### 二、SQL Server 与 PostgreSQL 函数差异 除了数据类型之外，还需要注意 SQL Server 和 PostgreSQL 在函数上的差异： - `isnull()` 在 PostgreSQL 中对应 `coalesce()` - `datalength()` 在 PostgreSQL 中对应 `octet_length()` - `len()` 在 PostgreSQL 中对应 `length()` - `convert()` 可以通过 `::type` 或 `cast()` 在 PostgreSQL 中实现 - 字符串连接操作，`'str1' + 'str2'` 在 PostgreSQL 中替换为 `'str1' || 'str2'` - `exists()` 函数在两个数据库系统中相同 - `ltrim()` 和 `rtrim()` 在 PostgreSQL 中可以通过 `trim()` 实现 - `lower()` 和 `upper()` 在两个数据库系统中相同 - 数学函数 `round(arg1,0)` 在 PostgreSQL 中可直接使用 `round(arg1)` - `floor(arg1)` 和 `ceil(arg1)` 在两个系统中均相同 #### 三、数组与字符串转换 在 PostgreSQL 中提供了强大的数组处理功能，包括数组与字符串之间的转换： - 使用 `lpad` 和 `rpad` 函数可以在字符串的左边或右边添加指定字符直到达到指定长度 - `string_to_array` 函数可以将字符串转换为数组 - `array_to_string` 函数可以将数组转换为字符串，并且支持指定分隔符 示例代码： ```sql -- 将字符串转换为数组 SELECT string_to_array('df,asd,fa', ','); -- 将数组转换为字符串 SELECT array_to_string(ARRAY['dfa', 'dfas', 'fds'], '?'); -- 使用 lpad 添加前导零 SELECT lpad('9', 6, '0'); ``` #### 四、PostgreSQL 特性介绍 除了以上提到的差异外，PostgreSQL 还拥有一些高级特性： 1. **事务管理**：PostgreSQL 支持多种事务隔离级别，可以更好地控制并发访问。 2. **窗口函数**：提供强大的分析能力，支持对数据进行复杂计算。 3. **多版本并发控制**（MVCC）：允许读写操作并行执行，提高系统吞吐量。 4. **分区表**：可以按照特定规则将大表拆分为多个小表，优化查询性能。 5. **全文搜索**：内置支持全文搜索功能，适用于构建搜索引擎等应用。 #### 五、特殊语法与注意事项 - **创建临时表**：在 SQL Server 中可以使用 `select * into #temp_table from table` 创建临时表，在 PostgreSQL 中则需使用 `create temp table temp_table on commit drop as select * from table`。 - **异常处理**：在 PostgreSQL 中可以使用 `BEGIN ... EXCEPTION WHEN ... THEN ... END` 结构来捕获并处理异常。 - **UUID 生成**：PostgreSQL 中需要先加载 `uuid-ossp` 模块才能使用 `uuid_generate_v1()` 生成 UUID。 从 SQL Server 迁移到 PostgreSQL 需要考虑数据类型映射、函数差异、以及 PostgreSQL 的特性和语法。通过合理的规划和调整，可以充分利用 PostgreSQL 的强大功能来构建高性能的应用系统。