PGSql No function matches the given name and argument types. You might need to add explicit type casts

Question

So, im in migrating database from Oracle to PGSql project, and now im fixing some query. i've got an error like this :

Caused by: org.postgresql.util.PSQLException: ERROR: function nvl(timestamp without time zone, timestamp with time zone) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

and i've found that nvl error from console on this string builder

    public List<InsEmployeeInfoVo> load(int first, int pageSize,
            String sortField, SortOrder sortOrder, SearchFilter searchFilter) {
        SQLQuery query = null;

        try {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.append(" select ");
            sqlQuery.append("     pts.PERSON_TRAN_SING_ID, ");
            sqlQuery.append("     ext.INTERNALID PERSON_EXT_ID, ");
            sqlQuery.append("     people.internalid PERSON_ID, ");
            sqlQuery.append("     people.EMPLOYEE_NUMBER NIK, ");
            sqlQuery.append("     people.PER_INFORMATION1 PERSON_NAME, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_MEMBER_NO is not null ");
            sqlQuery.append("         then pts.INS_MEMBER_NO ");
            sqlQuery.append("         else ext.INS_MEMBER_NO ");
            sqlQuery.append("     end INS_MEMBER_NO, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_TOTAL_COVER is not null ");
            sqlQuery.append("         then pts.INS_TOTAL_COVER ");
            sqlQuery.append("         else ext.INS_TOTAL_COVER ");
            sqlQuery.append("     end INS_TOTAL_COVER, ");
            sqlQuery.append("     null INS_PLAN_NAME, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_BH_CARD_NO is not null ");
            sqlQuery.append("         then pts.INS_BH_CARD_NO ");
            sqlQuery.append("         else ext.INS_BH_CARD_NO ");
            sqlQuery.append("     end INS_BH_CARD_NO, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_BH_TOTAL_COVER is not null ");
            sqlQuery.append("         then pts.INS_BH_TOTAL_COVER ");
            sqlQuery.append("         else ext.INS_BH_TOTAL_COVER ");
            sqlQuery.append("     end INS_BH_TOTAL_COVER, ");
            sqlQuery.append("   null INS_BH_PLAN_NAME, ");
            sqlQuery.append("     pts.PROCESS_STATUS PROCESS_STATUS, ");
            sqlQuery.append("     BTPN_INS_F_GET_CHECKER (people.internalid,:singSourceType,1) PROCESS_STATUS_NAME, ");
            sqlQuery.append("     pts2.PERSON_TRAN_SING_ID PERSON_TRAN_SING_ID_2, ");
            sqlQuery.append("     pts2.PROCESS_STATUS PROCESS_STATUS_2, ");
            sqlQuery.append("     upl.PERSON_DATA_UPL_DTL_ID, ");
            sqlQuery.append("     upl.PROCESS_STATUS PROCESS_STATUS_UPLOAD, ");
            sqlQuery.append("     NVL(pts.UPDATE_ON, pts.CREATE_ON) INPUT_DATE, ");
            sqlQuery.append("     BTPN_INS_F_GET_CHECKER (people.internalid,:singSourceType,2) NOTE_APPROVE, pts.NOTE ");
            sqlQuery.append(" from WOT_PEOPLE people ");
            sqlQuery.append("     inner join WOT_ASSIGNMENT assign ");
            sqlQuery.append("         on assign.PERSON_ID = people.internalid ");
            sqlQuery.append("         and assign.PRIMARY_FLAG = 'Y' ");
            sqlQuery.append("         and (date_trunc('day', now()) between assign.EFFECTIVE_START_DATE and assign.EFFECTIVE_END_DATE) ");
            sqlQuery.append("     left join BTPN_INS_PEOPLE_EXT ext ");
            sqlQuery.append("         on ext.PERSON_ID = people.internalid ");
            sqlQuery.append("         and date_trunc('day', now()) between ext.EFFECTIVE_START_DATE and NVL(ext.EFFECTIVE_END_DATE, now()) ");
            

i think something wrong with the last NVL variable but im not sure, im not usually use this native query and im new in PGSql too

Answer 1

You can try to use PostgreSQL COALESCE function (see the 9.17.2. COALESCE section) instead of oracle NVL function.