An Overview of DuckDB’s TYPEOF() Function

Posted on by Ian

DuckDB offers a variety of utility functions to help us better understand the data that we need to work with. Among these functions, typeof() serves as a useful tool for type inspection and validation. In this article, we’ll explore how this function works, along with examples that demonstrate its usage.

What is typeof()?

DuckDB’s typeof() function returns the data type of a given expression or column. This is the data type’s name returned as a string, so it’s easy for us to see exactly what the data type is.

The basic syntax goes like this:

typeof(expression);

So it accepts just one argument. It then returns the data type of that argument. The argument can be any type.

Example

Here’s a simple example to demonstrate how it works:

SELECT typeof(123);

Output:

INTEGER

Here’s what happens if I add a decimal point:

SELECT typeof(1.23);

Output:

DECIMAL(3,2)

And here’s what happens if I move the decimal point and add a fractional part:

SELECT typeof(123.456);

Output:

DECIMAL(6,3)

Converted Values

Let’s convert the value and compare the data type of the converted value to the original:

SET VARIABLE num = 123;
SELECT 
    typeof(getvariable('num')) AS original,
    typeof(cast(getvariable('num') AS double)) AS converted_1,
    typeof(cast(getvariable('num') AS varchar)) AS converted_2;

Result:

+----------+-------------+-------------+
| original | converted_1 | converted_2 |
+----------+-------------+-------------+
| INTEGER  | DOUBLE      | VARCHAR     |
+----------+-------------+-------------+

As expected, the typeof() function returned the relevant data types of the value after each conversion.

Database Example

Here’s a database example:

-- Create a sample table with various data types
CREATE TABLE type_examples(
    id INTEGER,
    name VARCHAR,
    price DECIMAL(10,2),
    is_available BOOLEAN,
    created_at TIMESTAMP,
    tags JSON
);

-- Insert a sample record
INSERT INTO type_examples VALUES 
(1, 'Laptop', 999.99, true, '2025-01-15 10:30:00', '["electronics", "computer"]');

-- Use typeof() to check column types
SELECT 
    typeof(id) as id_type,
    typeof(name) as name_type,
    typeof(price) as price_type,
    typeof(is_available) as bool_type,
    typeof(created_at) as timestamp_type,
    typeof(tags) as json_type
FROM type_examples;

Result:

+---------+-----------+---------------+-----------+----------------+-----------+
| id_type | name_type |  price_type   | bool_type | timestamp_type | json_type |
+---------+-----------+---------------+-----------+----------------+-----------+
| INTEGER | VARCHAR   | DECIMAL(10,2) | BOOLEAN   | TIMESTAMP      | JSON      |
+---------+-----------+---------------+-----------+----------------+-----------+

Handling NULL Values

The typeof() function still returns the expected data type even when applied to NULL values:

SELECT 
    typeof(NULL) AS orignal,
    typeof(NULL::INTEGER) AS converted;

Output:

+---------+-----------+
| orignal | converted |
+---------+-----------+
| "NULL"  | INTEGER   |
+---------+-----------+

Working with Expressions

The function works on expressions like the following:

SELECT 
    typeof(1 + 1),
    typeof(1.0 + 1),
    typeof('Moving' || ' Forward');

Result:

+-----------------+-------------------+----------------------------------+
| typeof((1 + 1)) | typeof((1.0 + 1)) | typeof(('Moving' || ' Forward')) |
+-----------------+-------------------+----------------------------------+
| INTEGER         | DECIMAL(12,1)     | VARCHAR                          |
+-----------------+-------------------+----------------------------------+

Comparing to Other Database Systems

DuckDB’s typeof() is similar to functionality provided in other database systems, but with some differences: