Solution to the problem of inserting Date type data error into PostgreSQL database

1|0 Problem Scenarios

This is an error that occurs when the company project makes a change to the project's database from Oracle to PostgreSQL version.

The console reports an error.

SQL: insert into table (...,RQ,...) select ...,?,... union all select ...,?,...
// (The other fields are omitted here, mainly because the RQ date type error is reported and it is not listed)
Cause: : Error: The type of field "rq" is timestamp without time zone, but the type of expression is text
Suggestion: You need to rewrite or convert expressions Location: 404

The dynamic SQL in the mapper file is

<insert  parameterType="">
  insert into table
  (
  -- Omitted,
  RQ,
  -- Omitted)
  <foreach collection="list" item="item" separator="union all">
    select
    -- Omitted,
    #{,jdbcType=TIMESTAMP},
    -- Omitted
    -- from dual This was the projectOracleDatabase conversion toPostgreSQLdatabase
  </foreach>
</insert>

The corresponding entity class is

@Data
public class Entity {
    // Omitted    private Date rq;
    // Omitted}

When inserting data into the database, the field RQ type error will be reported, but no obvious error will be found after checking the SQL statement.

Although the console reports an error, it can still insert several pieces of data into the database, and then it reports an error and cannot be inserted. At this time, considering the possibility of inserting data, but here#{,jdbcType=TIMESTAMP}, the data type also conforms to the date type timestamp of the PostgreSQL database, so it is not a problem with this.

At this time, I cleared the data in the database, and after re-executing the program several times, I found that sometimes I could insert several pieces of data into the database, and then I reported an error. Sometimes I reported an error without inserting one piece of data. It was very strange.

2|0 Solution

PostgreSQL is used later, and the specific syntax is added after rq::timestampIt can be inserted normally, as follows

<insert  parameterType="">
  insert into table
  (
  -- Omitted,
  RQ,
  -- Omitted)
  <foreach collection="list" item="item" separator="union all">
    select
    -- Omitted,
    #{,jdbcType=TIMESTAMP}::timestamp,
    -- Omitted
    -- from dual -- here it isOracleDatabase syntax
  </foreach>
</insert>

But in this way, this will add this to the field for each date, which is very troublesome to change, so I'm wondering if there is a better solution.

Later I changed this batch insert dynamic sql. Since this SQL can run normally in Oracle database, there may be compatibility problems in PostgreSQL database, so it has been changed to dynamic SQL that is often written when using MySQL database, as follows:

<insert  parameterType="">
        insert into table (
        -- Omitted,
        RQ,
        -- Omitted)
        values
        <foreach collection="list" item="item" separator=",">
            (
            -- Omitted,
            #{,jdbcType=TIMESTAMP},
            -- Omitted
            )
        </foreach>
    </insert>

After re-executing the program, no errors were reported, and the data was inserted normally, which was a perfect solution!

This is the article about inserting Date type data errors into PostgreSQL database. For more information about inserting Date type data errors into PostgreSQL database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!