Several pits of Ruoyi switching from mysql to postgresql

Because of the needs of engineering applications, ruoyi's database support needs to be switched from mysql to postgresql.There are also some open source demos or integrated frameworks on the Internet that indicate that the database has been switched.This article will briefly introduce several problems that may be encountered when switching databases, and how to solve them.

Although they are both relational databases, mysql and postgresql are not very compatible in terms of sql compatibility, and many syntaxes supported by mysql are not known in postgresql.

Less gossip, let's enter the text below.

1. Replace the mysql-dependent jar package with postgresql in the pom.xml file.

    org.postgresql   postgresql

Second, mybatis-plus or paging plug-in database category support.

The characters in the red boxes in the two places aboveto be replaced.

Three, the database connection pool should be adjusted accordingly

Fourth, automatic task dependencies, if you have enabled the quartz component, please remember to open the following comments​​​​​​​​

//pg enables special configuration, otherwise an error will be reported update by wuzuhu on 20181224//prop.put("org.quartz.jobStore.driverDelegateClass", "org.quartz.impl.jdbcjobstore.PostgreSQLDelegate");

5. In all Mapper.xml configuration files, find the function with sysdate(), which needs to be replaced with the now() function.

6. All query functions with date need to be replaced with the following code:

7. The mysql find_in_set() function can use the ANY() scheme.For example:

#{deptId} || '' = ANY(STRING_TO_ARRAY(ancestors, ','))

Summary: The above steps can basically complete the switch of Ruoyi's database from mysql to postgresql, I hope it can help you.If you have a better migration plan, please leave a message to exchange.