title: PGSql No function matches the given name and argument types. You might need to add explicit type casts
body: <p>So, im in migrating database from Oracle to PGSql project, and now im fixing some query.
  i've got an error like this :</p>
  <pre><code>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.
  </code></pre>
  <p>and i've found that nvl error from console on this string builder</p>
  <pre><code>    public List&lt;InsEmployeeInfoVo&gt; load(int first, int pageSize,
              String sortField, SortOrder sortOrder, SearchFilter searchFilter) {
          SQLQuery query = null;
  
          try {
              StringBuilder sqlQuery = new StringBuilder();
              sqlQuery.append(&quot; select &quot;);
              sqlQuery.append(&quot;     pts.PERSON_TRAN_SING_ID, &quot;);
              sqlQuery.append(&quot;     ext.INTERNALID PERSON_EXT_ID, &quot;);
              sqlQuery.append(&quot;     people.internalid PERSON_ID, &quot;);
              sqlQuery.append(&quot;     people.EMPLOYEE_NUMBER NIK, &quot;);
              sqlQuery.append(&quot;     people.PER_INFORMATION1 PERSON_NAME, &quot;);
              sqlQuery.append(&quot;     case &quot;);
              sqlQuery.append(&quot;         when pts.INS_MEMBER_NO is not null &quot;);
              sqlQuery.append(&quot;         then pts.INS_MEMBER_NO &quot;);
              sqlQuery.append(&quot;         else ext.INS_MEMBER_NO &quot;);
              sqlQuery.append(&quot;     end INS_MEMBER_NO, &quot;);
              sqlQuery.append(&quot;     case &quot;);
              sqlQuery.append(&quot;         when pts.INS_TOTAL_COVER is not null &quot;);
              sqlQuery.append(&quot;         then pts.INS_TOTAL_COVER &quot;);
              sqlQuery.append(&quot;         else ext.INS_TOTAL_COVER &quot;);
              sqlQuery.append(&quot;     end INS_TOTAL_COVER, &quot;);
              sqlQuery.append(&quot;     null INS_PLAN_NAME, &quot;);
              sqlQuery.append(&quot;     case &quot;);
              sqlQuery.append(&quot;         when pts.INS_BH_CARD_NO is not null &quot;);
              sqlQuery.append(&quot;         then pts.INS_BH_CARD_NO &quot;);
              sqlQuery.append(&quot;         else ext.INS_BH_CARD_NO &quot;);
              sqlQuery.append(&quot;     end INS_BH_CARD_NO, &quot;);
              sqlQuery.append(&quot;     case &quot;);
              sqlQuery.append(&quot;         when pts.INS_BH_TOTAL_COVER is not null &quot;);
              sqlQuery.append(&quot;         then pts.INS_BH_TOTAL_COVER &quot;);
              sqlQuery.append(&quot;         else ext.INS_BH_TOTAL_COVER &quot;);
              sqlQuery.append(&quot;     end INS_BH_TOTAL_COVER, &quot;);
              sqlQuery.append(&quot;   null INS_BH_PLAN_NAME, &quot;);
              sqlQuery.append(&quot;     pts.PROCESS_STATUS PROCESS_STATUS, &quot;);
              sqlQuery.append(&quot;     BTPN_INS_F_GET_CHECKER (people.internalid,:singSourceType,1) PROCESS_STATUS_NAME, &quot;);
              sqlQuery.append(&quot;     pts2.PERSON_TRAN_SING_ID PERSON_TRAN_SING_ID_2, &quot;);
              sqlQuery.append(&quot;     pts2.PROCESS_STATUS PROCESS_STATUS_2, &quot;);
              sqlQuery.append(&quot;     upl.PERSON_DATA_UPL_DTL_ID, &quot;);
              sqlQuery.append(&quot;     upl.PROCESS_STATUS PROCESS_STATUS_UPLOAD, &quot;);
              sqlQuery.append(&quot;     NVL(pts.UPDATE_ON, pts.CREATE_ON) INPUT_DATE, &quot;);
              sqlQuery.append(&quot;     BTPN_INS_F_GET_CHECKER (people.internalid,:singSourceType,2) NOTE_APPROVE, pts.NOTE &quot;);
              sqlQuery.append(&quot; from WOT_PEOPLE people &quot;);
              sqlQuery.append(&quot;     inner join WOT_ASSIGNMENT assign &quot;);
              sqlQuery.append(&quot;         on assign.PERSON_ID = people.internalid &quot;);
              sqlQuery.append(&quot;         and assign.PRIMARY_FLAG = 'Y' &quot;);
              sqlQuery.append(&quot;         and (date_trunc('day', now()) between assign.EFFECTIVE_START_DATE and assign.EFFECTIVE_END_DATE) &quot;);
              sqlQuery.append(&quot;     left join BTPN_INS_PEOPLE_EXT ext &quot;);
              sqlQuery.append(&quot;         on ext.PERSON_ID = people.internalid &quot;);
              sqlQuery.append(&quot;         and date_trunc('day', now()) between ext.EFFECTIVE_START_DATE and NVL(ext.EFFECTIVE_END_DATE, now()) &quot;);
              
  </code></pre>
  <p>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</p>