How to check type of value in postgres

sql

conditional-statements

postgresql

type-checking

byNikita Barsukov·Dec 19, 2024

To find out the data type of a value in PostgreSQL, use the pg_typeof() function:

-- Example with a literal:
SELECT pg_typeof('Sample text');

Returns: text

-- Example with a table column. 
-- Now, who's hiding there in the column?
SELECT pg_typeof(column_name) FROM table_name;

Output: Data type of entity hiding in column_name e.g., integer, boolean.

Conditional operations based on type

With the help of CASE statement, decide which action based on value type:

-- Like choosing pizza toppings based on the crust type 
SELECT 
    CASE
        WHEN pg_typeof(col) = 'integer' THEN 'Perform integer operation'
        WHEN col ~ '^[0-9]+$' THEN 'String is an integer disguise'
        ELSE 'This pizza crust doesn't support that topping'
    END 
FROM table_name;

Diving deep: Explore PostgreSQL's system catalogs

Looking to go on an Indiana Jones adventure raiding PostgreSQL's warehouse of data types? Our faithful guides are pg_type and pg_catalog.pg_namespace.

-- Indiana Jones and the Query of Enum Types
SELECT 
    n.nspname as schema, 
    t.typname as enum_name, 
    e.enumlabel as enum_value
FROM 
    pg_type t 
    JOIN pg_enum e ON t.oid = e.enumtypid  
    JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
ORDER BY 
    enum_name, enum_value;

Handling tricky cases and pitfalls

pg_typeof is a great level one wizard spell, but sometimes you need a level two (or even higher) spell for complex type invocations.

SELECT 
    CASE
        WHEN trim(column_name) SIMILAR TO '[0-9]+' THEN column_name::int
        ELSE NULL "Yeah, no integer for you"
    END 
FROM table_name;

Exceptions: The bodyguard of type casting

Like the bodyguard who takes the bullet, exceptions shield your cast operation if it fails:

BEGIN
    -- Try the integer costume
    my_variable := varchar_column::integer;
EXCEPTION WHEN others THEN
    -- "Sorry, this costume doesn't fit you"
    -- Handle failure to cast here
END;