Usually when we run a SQL query it’s important to know the data type of the columns returned by that query. If we’re familiar with the data, then we might instinctively know the data type of each column. But even then, we might only have a general idea, such as “it’s a date” or “it’s a number” without knowing the exact details.

Fortunately, DuckDB provides us with several ways to find out the data type of the columns returned by a query.

1. Using duckbox Mode in the DuckDB CLI

First of all, if you’re using the DuckDB CLI, you can set the output mode to duckbox. When we use duckbox mode, each column’s data type is displayed in the column header of the resultset. Actually, duckbox is the default output mode in the DuckDB CLI, and so you’re probably already aware of this if you’ve ever used the DuckDB CLI.

That said, here’s an example of enabling duckbox mode and running a query in the DuckDB CLI:

.mode duckbox
SELECT * FROM type_examples;

The result might look something like this:

+-------+-----------+----------------+--------------+---------------------+-----------------------------+
|  id   |   name    |     price      | is_available |     created_at      |            tags             |
| int32 |  varchar  | decimal(10,2)  |   boolean    |      timestamp      |            json             |
+-------+-----------+----------------+--------------+---------------------+-----------------------------+
|   1   |  Laptop   |     999.99     |    true      | 2025-01-15 10:30:00 | ["electronics", "computer"] |
+-------+-----------+----------------+--------------+---------------------+-----------------------------+

We can see the data type of each column in the column header, under the column’s name.

2. The typeof() Function

If you’re not using the DuckDB CLI, or if duckbox mode isn’t an option, then you can always use the typeof() function. This function’s sole purpose is to return the data type of its argument. This function works just like SQLite’s typeof() function.

Example:

SELECT typeof(123);

Output:

INTEGER

Here it is against a database table:

SELECT 
    typeof(id) AS id,
    typeof(name) AS name,
    typeof(price) AS price,
    typeof(is_available) AS is_available,
    typeof(created_at) AS created_at,
    typeof(tags) AS tags
FROM type_examples;

Output:

+---------+---------+---------------+--------------+------------+------+
|   id    |  name   |     price     | is_available | created_at | tags |
+---------+---------+---------------+--------------+------------+------+
| INTEGER | VARCHAR | DECIMAL(10,2) | BOOLEAN      | TIMESTAMP  | JSON |
+---------+---------+---------------+--------------+------------+------+

This is the same table as the one in the previous example (when we used duckbox mode). We can see that the data types are the same. The only difference is that in this example, the first column returned INTEGER, whereas in the previous example we got int32 (which is actually just an alias for INTEGER).

3. The pg_typeof() Function

DuckDB provides the pg_typeof() function for compatibility with PostgreSQL. In case you’re not aware, PostgreSQL has a pg_typeof() function that works just like DuckDB’s typeof() function, except that it returns its result in lowercase. Therefore, for full compatibility, DuckDB’s implementation of pg_typeof() returns its result in lowercase too:

SELECT pg_typeof(123);

Output:

integer

Here it is against a database table:

SELECT 
    pg_typeof(id) AS id,
    pg_typeof(name) AS name,
    pg_typeof(price) AS price,
    pg_typeof(is_available) AS is_available,
    pg_typeof(created_at) AS created_at,
    pg_typeof(tags) AS tags
FROM type_examples;

Output:

+---------+---------+---------------+--------------+------------+------+
|   id    |  name   |     price     | is_available | created_at | tags |
+---------+---------+---------------+--------------+------------+------+
| integer | varchar | decimal(10,2) | boolean      | timestamp  | json |
+---------+---------+---------------+--------------+------------+------+

So, exactly like typeof() but with lowercase output.

4. Using DESCRIBE or DESCRIBE SELECT Statement

We can use the DESCRIBE or DESCRIBE SELECT statement to return the data types of a table or query respectively.

If we want the data type of all columns in a table, we can use DESCRIBE, like this:

DESCRIBE type_examples;

The result looks something like this:

+--------------+---------------+------+------+---------+-------+
| column_name  |  column_type  | null | key  | default | extra |
+--------------+---------------+------+------+---------+-------+
| id           | INTEGER       | YES  | NULL | NULL    | NULL  |
| name         | VARCHAR       | YES  | NULL | NULL    | NULL  |
| price        | DECIMAL(10,2) | YES  | NULL | NULL    | NULL  |
| is_available | BOOLEAN       | YES  | NULL | NULL    | NULL  |
| created_at   | TIMESTAMP     | YES  | NULL | NULL    | NULL  |
| tags         | JSON          | YES  | NULL | NULL    | NULL  |
+--------------+---------------+------+------+---------+-------+

This returned information about all columns in the table.

If we only want information about select columns, we can use DESCRIBE SELECT. This enables us to get the data types from the resultset of a query (not just from a single table). We can also use it to return information about a subset of columns in a table.

Example:

DESCRIBE SELECT 
    price, 
    created_at, 
    tags 
FROM type_examples;

Result:

+-------------+---------------+------+------+---------+-------+
| column_name |  column_type  | null | key  | default | extra |
+-------------+---------------+------+------+---------+-------+
| price       | DECIMAL(10,2) | YES  | NULL | NULL    | NULL  |
| created_at  | TIMESTAMP     | YES  | NULL | NULL    | NULL  |
| tags        | JSON          | YES  | NULL | NULL    | NULL  |
+-------------+---------------+------+------+---------+-------+

This time we only got information about three columns in the table.

5. Using PRAGMA table_info()

We can use PRAGMA table_info() to get information about a table, including data types:

PRAGMA table_info('type_examples');

Result:

+-----+--------------+---------------+---------+------------+-------+
| cid |     name     |     type      | notnull | dflt_value |  pk   |
+-----+--------------+---------------+---------+------------+-------+
| 0   | id           | INTEGER       | false   | NULL       | false |
| 1   | name         | VARCHAR       | false   | NULL       | false |
| 2   | price        | DECIMAL(10,2) | false   | NULL       | false |
| 3   | is_available | BOOLEAN       | false   | NULL       | false |
| 4   | created_at   | TIMESTAMP     | false   | NULL       | false |
| 5   | tags         | JSON          | false   | NULL       | false |
+-----+--------------+---------------+---------+------------+-------+

This option only works on tables, and so its appeal is probably more limited than the other methods. That said, it’s still an option.