向PostgreSQL資料庫插入Date型別資料包錯

errorsG發表於2024-12-05
SQL資料庫型別

問題場景

這個是公司專案進行更改，將專案的資料庫從Oracle版本改為PostgreSQL版本時發生的錯誤。

控制檯報錯內容。

SQL: insert into table (...,RQ,...) select ...,?,... union all select ...,?,...
//（這裡的...省略了其他欄位, 主要是RQ日期型別報錯就沒列出）
Cause: org.postgresql.util.PSQLException: 錯誤: 欄位 "rq" 的型別為 timestamp without time zone, 但表示式的型別為 text
建議：你需要重寫或轉換表示式 位置：404

mapper檔案中的動態sql為

<insert id="insert" parameterType="java.util.List">
  insert into table
  (
  -- 省略,
  RQ,
  -- 省略)
  <foreach collection="list" item="item" separator="union all">
    select
    -- 省略,
    #{item.rq,jdbcType=TIMESTAMP},
    -- 省略
    -- from dual 這裡當時是將專案Oracle資料庫轉為PostgreSQL資料庫
  </foreach>
</insert>

對應的實體類為

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

當在插入資料到資料庫的時候就會報欄位RQ型別錯誤的問題，但是透過檢查sql語句後並沒有發現明顯的錯誤。

雖然控制檯報錯了，但還是能插入幾條資料到資料庫中，然後就報錯不能插入了。這個時候考慮到有可能插入資料的問題，但是這裡設定了#{item.rq,jdbcType=TIMESTAMP}，資料型別也是符合PostgreSQL資料庫的日期型別timestamp的，所以也不是這個的問題。

這個時候我將資料庫中的資料清除，重新執行了幾遍程式後發現有時候能夠向資料庫中插入幾條資料，然後就報錯，有時候一條資料都沒有插入就報錯了，很是奇怪。

解決方法

後面使用了PostgreSQL的特定的語法，在rq後面加上::timestamp就能夠正常插入了，如下

<insert id="insert" parameterType="java.util.List">
  insert into table
  (
  -- 省略,
  RQ,
  -- 省略)
  <foreach collection="list" item="item" separator="union all">
    select
    -- 省略,
    #{item.rq,jdbcType=TIMESTAMP}::timestamp,
    -- 省略
    -- from dual -- 這裡是Oracle資料庫的語法
  </foreach>
</insert>

但這樣每個日期的欄位都要加上這個，更改起來很麻煩，所以我在想有沒有更好的解決方案。

後面我更改了這一條批次插入的動態sql。由於這個sql在Oracle資料庫中是能夠正常的執行的，有可能在PostgreSQL資料庫存在相容的問題，所以改成了在使用Mysql資料庫時常寫的動態sql，如下：

<insert id="inser" parameterType="java.util.List">
        insert into table (
        -- 省略,
        RQ,
        -- 省略)
        values
        <foreach collection="list" item="item" separator=",">
            (
            -- 省略,
            #{item.rq,jdbcType=TIMESTAMP},
            -- 省略
            )
        </foreach>
    </insert>

重新執行程式後，沒有報錯，資料正常插入，完美解決！

相關文章