amazon-redshiftlpad

LPAD function errors when used in WITH variable in Redshift

Can you tell me why this is throwing an error in Redshift?

WITH Testing_PADDING AS (SELECT '12345678' AS column1)
SELECT LPAD(column1, 9,'0') FROM Testing_PADDING;

Here is the error I receive:

"Invalid operation: failed to find conversion function from "unknown" to text;"

Solution

  • Redshift can't determine data type from the context, so you need to explicitly set it

    WITH Testing_PADDING AS (SELECT '12345678'::text AS column1)
SELECT
    LPAD(column1, 9, '0')
FROM Testing_PADDING;