MacLochlainns Weblog

While porting my Oracle code to PostgreSQL I encountered a little quirk. It’s probably not a quirk except for the fact that I’ve worked in Oracle so long. Oracle implicitly type casts so well that we seldom notice.

PostreSQL doesn’t work like Oracle. It does type cast sometimes but not very often. I tried porting the following segment from my Oracle stored procedure to PostgreSQL:

/* Add account number with zeros. */
FOR j IN 1..50 LOOP
  INSERT INTO account_list
  VALUES
  ( lv_airport_code||'-'||LPAD(j,6,'0')
  , NULL
  , NULL
  , 1002
  , 1002 );
END LOOP;

Oracle implicitly casts the integer j to a text string before running the LPAD function. In PostgreSQL, it doesn’t cast the integer to a text string before calling the LPAD function. Since the LPAD function is not overloaded, calling it with an integer, integer, and text set of parameters fails at runtime but the code doesn’t raise an exception when compiling it as a stored procedure.

At runtime, it raises the following error:

ERROR:  invalid INPUT syntax FOR INTEGER: "function lpad(integer, integer, unknown) does not exist"
CONTEXT:  PL/pgSQL FUNCTION seed_account_list() line 48 at assignment

You can fix the code by explicitly casting the LPAD function’s first parameter to a text string. You do that as follows below:

/* Add account number with zeros. */
FOR j IN 1..50 LOOP
  INSERT INTO account_list
  VALUES
  ( lv_airport_code||'-'||LPAD(j::text,6,'0')
  , NULL
  , NULL
  , 1002
  , 1002 );
END LOOP;

This fixes the casting problem. As always, I hope this helps those looking for a solution.