Monday, 11 November 2019 23:08

PostgreSQL and LPAD

Written by http://blog.mclaughlinsoftware.com/2019/11/12/postgresql-and-lpad/
Rate this item
(0 votes)

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 IN1..50LOOPINSERTINTO account_list VALUES( lv_airport_code||'-'||LPAD(j,6,'0'),NULL,NULL,1002,1002);ENDLOOP;

/* 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 FORINTEGER: "function lpad(integer, integer, unknown) does not exist"CONTEXT: PL/pgSQL FUNCTION seed_account_list() line 48 at assignment

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 IN1..50LOOPINSERTINTO account_list VALUES( lv_airport_code||'-'||LPAD(j::text,6,'0'),NULL,NULL,1002,1002);ENDLOOP;

/* 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.

Read 35 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.