Mysql转PostgreSQL注意事项及说明！

2024-12-6

Mysql转PostgreSQL注意事项及说明！

这篇文章主要介绍了Mysql转PostgreSQL注意事项及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教。

Mysql转PostgreSQL注意事项

- ifnull()和COALESCE()

mysql--ifnull()
改pg--COALESCE()
ps:
mysql -- ifnull(a.audit_result, '')
pgsql -- COALESCE(a.audit_result, '')

- date_format()和to_date()

mysql--date_format()
改pg--to_date(),声明类型 ‘ ::text'
ps：
//pg数据库中不能使用'%',如 %y-%m-%d
to_date(create_time::text, 'YY-MM-DD')

- find_in_set()和ANY (string_to_array(‘’, ‘,’))

mysql--find_in_set()
改pg--ANY (string_to_array(some_column, ','))
ps:
SELECT t.dept_id FROM sys_dept t WHERE find_in_set('100', ancestors)
SELECT t.dept_id FROM sys_dept t WHERE '100' = ANY (string_to_array(ancestors, ','))

- sysdate()和now()

mysql--sysdate()
改pg--now()

- 模糊匹配

// 如果使用 concat参数，pg数据库需配置隐形转换类型
mysql-- like concat('%', #{testItem}, '%') 
改pg-- ilike '%'|| #{testItem} ||'%'  或  like concat('%', #{testItem}, '%')

- GROUP_CONCAT()和string_agg()

mysql-- GROUP_CONCAT(t.cname)
改pg-- array_to_string(array_agg(t.cname),',') 或 string_agg(t.cname,',')

- locate()和strpos()

mysql-- locate()
改pg-- strpos()

CRUD问题

选择 id 在的逗号分隔的字符串所表示的数组中的行

如果要将字符串用于连接条件，则需要将字符串转换为适当的整数数组。

Select * 
from table_name
where id = any(string_to_array(?, ',')::int[]);

string_to_array(?, ',')::int[] 这部分代码的作用是将一个逗号分隔的字符串转换为一个整数数组。? 是一个占位符，你需要在执行这个查询时提供一个实际的值。例如，如果你提供的值是 '1,2,3'，那么 string_to_array(?, ',')::int[] 就会返回一个数组 [1, 2, 3]。

a.id = ANY(...) 这部分代码的作用是检查 a.id 是否在给定的数组中。如果 a.id 的值在数组中，那么这个 WHERE 条件就会为真，相应的行就会被选中。

所以，整个查询的意思是：从 table_name 表中选择那些 id 在给定的逗号分隔的字符串所表示的数组中的行

int可根据需要，替换为 bigint

timestamp日期与字符串比较

List selectList(@Param(“startTime”) String startTime);

使用::双冒号进行类型转换

<select id="selectList" resultType="com.entity.UserInfo" parameterType="java.util.List">
    SELECT *
    from user_info
    WHERE
    create_time &gt;= #{startTime}::timestamp
    and
    create_time &lt;= '2020-07-07 16:35:02'
</select>

PS:

#{startTime}::timestamp 可根据需要，替换为 date

timestamp：时间戳，转化后带时分秒

date：日期类型，转化后不带时分秒

字符串与数值类型不匹配无法比较

SQL: SELECT b.* from b_station_charts_field b WHERE station_type = ?

Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer

这个错误通常是因为在条件中比较了一个字符类型（varchar）的列与一个整数类型的值，导致数据库无法找到对应的操作符。要解决这个问题，可以将条件中的参数值转换为正确的数据类型。

例如，如果要比较的是一个字符类型的列 station_type 和一个整数类型的参数值，可以使用 ::integer 运算符将参数值转换为整数类型，示例代码如下：

SELECT b.*
FROM b_station_charts_field b
WHERE station_type = ?::integer;

请注意，以上示例中的 ? 是一个占位符，需要将其替换为实际的参数值。

如果要比较的是一个整数类型的列和一个字符类型的参数值，可以使用 ::varchar 运算符将列的值转换为字符类型，示例代码如下：

SELECT b.*
FROM b_station_charts_field b
WHERE station_type::varchar = ?;

同样的，需要将以上示例中的 ? 替换为实际的参数值。

使用字符串类型更新时间戳失败

Cause: org.postgresql.util.PSQLException: ERROR: column “rowmodifytime” is of type timestamp without time zone but expression is of type character varying

在更新 PostgreSQL 数据库时，尝试将一个字符类型的值赋给一个时间戳类型的列，这是不允许的。

需要确保 rowModifyTime 的值是一个有效的时间戳，而不是一个字符串。可以使用 TO_TIMESTAMP 函数将字符串转换为时间戳，如下所示：

UPDATE b_car_table 
SET
    rowModifyTime=TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS'),
WHERE vehicleId=?;

在这个例子中，TO_TIMESTAMP 函数将字符串转换为时间戳，需要将 ? 替换为时间字符串，格式为 ‘YYYY-MM-DD HH24:MI:SS’。

自动将驼峰转成了全部小写，数据库中的字段为deptId

org.postgresql.util.PSQLException: ERROR: column “deptid” of relation “b_station” does not exist

PostgreSQL 默认将标识符（如表名、列名）转换为小写。这就是为什么在数据库中定义的列名为 “deptId”，但在查询时自动转换为了小写的 “deptid”。

如果希望在查询时保留驼峰命名的大小写，可以使用双引号将列名括起来。例如，可以这样编写查询语句：

SELECT "deptId" FROM b_station;

使用双引号将列名括起来后，PostgreSQL 将不会对该列名进行转换，而会按照指定的大小写进行匹配。

函数不存在

ifnull 函数不存在

org.postgresql.util.PSQLException: ERROR: function ifnull(character varying, unknown) does not exist

在 PostgreSQL 数据库中使用 IFNULL 函数，但是 PostgreSQL 并不支持这个函数。IFNULL 是 MySQL 的函数，用于检查第一个表达式是否为 NULL，如果为 NULL，则返回第二个表达式的值。

在 PostgreSQL 中，可以使用 COALESCE 函数来达到类似的效果。以下是一个例子：

SELECT COALESCE(column_name, 'default_value') 
FROM table_name;

在这个例子中，COALESCE(column_name, 'default_value') 将检查 column_name 是否为 NULL，如果为 NULL，则返回 'default_value'。table_name 是表名，column_name 是想要检查的列。

如果在 Java 代码中使用 IFNULL 函数，需要将其替换为 COALESCE 函数，并确保 JDBC 查询字符串正确地使用了这个函数。

if函数不存在

改造 IF(bcard.status=‘2’,‘已激活’,‘未激活’)

在 PostgreSQL 中，可以使用 CASE 表达式对条件进行判断和转换。以下是将原 IF 表达式改写为 CASE 表达式的示例：

CASE
   WHEN bcard.status = '2' THEN '已激活'
   ELSE '未激活'
END

在上面的代码中，我们使用 CASE 表达式来根据条件对 bcard.status 的值进行判断。如果 bcard.status 的值等于 ‘2’，则返回 ‘已激活’，否则返回 ‘未激活’。

group_concat函数不存在

Cause: org.postgresql.util.PSQLException: ERROR: function group_concat(character varying) does not exist

在 PostgreSQL 数据库中使用 group_concat 函数，但是 PostgreSQL 并不支持这个函数。group_concat 是 MySQL 的函数，用于将多个行的数据连接成一个字符串。

在 PostgreSQL 中，可以使用 string_agg 函数来达到类似的效果。以下是一个例子：

SELECT string_agg(column_name, ',') 
FROM table_name 
GROUP BY group_column;

在这个例子中，string_agg 函数将 column_name 的所有行连接成一个以逗号分隔的字符串。table_name 是表名，group_column 是想要按照哪个列进行分组。

unix_timestamp函数不存在

org.postgresql.util.PSQLException: ERROR: function unix_timestamp(timestamp with time zone) does not exist

在 PostgreSQL 数据库中使用 unix_timestamp 函数，但是 PostgreSQL 并不支持这个函数。unix_timestamp 是 MySQL 的函数，用于将日期或日期时间值转换为 Unix 时间戳。

在 PostgreSQL 中，可以使用 EXTRACT 函数和 EPOCH 来达到类似的效果。以下是一个例子：

SELECT EXTRACT(EPOCH FROM timestamp_column) 
FROM table_name;

在这个例子中，EXTRACT(EPOCH FROM timestamp_column) 将 timestamp_column 的值转换为 Unix 时间戳。table_name 是表名，timestamp_column 是时间戳列。

date_format函数不存在

Cause: org.postgresql.util.PSQLException: ERROR: function date_format(timestamp without time zone, unknown) does not exist

在 PostgreSQL 数据库中使用 date_format 函数，但是 PostgreSQL 并不支持这个函数。date_format 是 MySQL 的函数，用于格式化日期或日期时间值。

在 PostgreSQL 中，可以使用 TO_CHAR 函数来达到类似的效果。以下是一个例子：

SELECT TO_CHAR(timestamp_column, 'YYYY-MM-DD HH24:MI:SS') 
FROM table_name;

在这个例子中，TO_CHAR(timestamp_column, ‘YYYY-MM-DD HH24:MI:SS’) 将 timestamp_column 的值格式化为 ‘YYYY-MM-DD HH24:MI:SS’ 格式。table_name 是表名，timestamp_column 是时间戳列。

如果在 Java 代码中使用 date_format 函数，需要将其替换为 TO_CHAR 函数，并确保 JDBC 查询字符串正确地使用了这个函数。

find_in_set 函数不存在

你遇到的问题是在 PostgreSQL 数据库中使用 FIND_IN_SET 函数，但是 PostgreSQL 并不支持这个函数。FIND_IN_SET 是 MySQL 的函数，用于在逗号分隔的列表中查找值。

在 PostgreSQL 中，你可以使用 ANY 或 = ANY 来达到类似的效果。以下是一个例子：

SELECT * 
FROM table_name
WHERE column_name = ANY(ARRAY[1,2,3]);

在这个例子中，column_name = ANY(ARRAY[1,2,3]) 将查找 column_name 是否在数组 [1,2,3] 中。table_name 是你的表名，column_name 是你想要查找的列。

如果你在 Java 代码中使用 FIND_IN_SET 函数，你需要将其替换为 ANY 或 = ANY，并确保你的 JDBC 查询字符串正确地使用了这个函数。

总结

以上为个人经验，希望能给大家一个参考。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！

更多技巧， www.excelbook.cn

欢迎加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

阅读剩余

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END

Python获取屏幕截图区域坐标简单代码和思路！

<<上一篇

关闭 WIN11的VBS 功能，提高25%性能！

下一篇>>