How to know the type of a value returned by a Redshift query?

Question

In PostgreSQL, you can use the funtion pg_typeof() within the SELECT statement to get the data type of a value returned by a SQL query.

This function does not exist in Redshift, despite Redshift's SQL lineage.

I want to do this:

SELECT pg_typeof(0.25::numeric(5,2)) ;

and get:

numeric(5,2)

How do I get the data type of a value returned by a SQL query? This is to help me with unit-testing my ETL development.

Answer 1

Not sure if can be used in you use case, but I use this query to get the types in Redshift

SELECT distinct 
        t.relname as table,
        a.attname as column,
        pg_catalog.format_type(a.atttypid, a.atttypmod) as type
FROM pg_attribute a
    JOIN pg_class t on a.attrelid = t.oid
    JOIN pg_namespace s on t.relnamespace = s.oid
WHERE a.attnum > 0 
    AND NOT a.attisdropped
    AND LENGTH(a.attname) > 1
    AND s.nspname in ({{schemas}})