LPAD function errors when used in WITH variable in Redshift

Question

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;"

Answer 1

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;

Answer 2

I suspect that one of your strings isn't being seen as text - likely the column1 text. (Sorry don't have a cluster up not to test)

Try:

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