The LENGTH() function

Informix^® provides the LENGTH() function:

SELECT LENGTH("aaa"), LENGTH(col1) FROM table

PostgreSQL has a equivalent function with the same name, but there is some difference:

Informix does not count the trailing blanks for CHAR or VARCHAR expressions, while PostgreSQL counts the trailing blanks.

With the PostgreSQL LENGTH function, when using a CHAR column values are always blank padded, so the function returns the size of the CHAR column. When using a VARCHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.

PostgreSQL raises an error if the LENGTH() parameter is NULL. Informix returns zero instead.

Solution

The PostgreSQL database interface cannot simulate the behavior of the Informix LENGTH() SQL function.

Review the program logic and make sure you do not pass NULL values to the LENGTH() SQL function.

You must check if the trailing blanks are significant when using the LENGTH() function.

If you want to count the number of character by ignoring the trailing blanks, you must use the RTRIM() function.