TYPEOF() vs PG_TYPEOF() in DuckDB: What’s the Difference?

by

You may be aware that DuckDB includes a typeof() function that works just like the SQLite equivalent; it returns the data type of its argument. But did you know that DuckDB also provides us with a pg_typeof() function that does essentially the same thing?

So why would DuckDB need a pg_typeof() function that does basically the same thing as typeof()? Let’s find out!

The Difference

Let’s cut to the chase. The difference between these two functions can be summed up by their definitions as outlined in the DuckDB documentation:

  • typeof(): Returns the name of the data type of the result of the expression.
  • pg_typeof(): Returns the lower case name of the data type of the result of the expression. For PostgreSQL compatibility.

So pg_typeof() is really just a PostgreSQL compatible version of typeof(). To clarify, PostgreSQL has a pg_typeof() function that works just like DuckDB’s typeof() function, except that it returns its result in lowercase.

So the main difference between the two DuckDB functions is that typeof() returns its result in uppercase, while pg_typeof() returns its result in lowercase.

Example

Here’s an example that shows the difference between the output of the two functions:

SELECT 
    typeof(123) AS typeof,
    pg_typeof(123) AS pg_typeof;

Result:

+---------+-----------+
| typeof  | pg_typeof |
+---------+-----------+
| INTEGER | integer   |
+---------+-----------+

By the way, it doesn’t matter which case we use when converting a value to another data type, the functions still output their results using their designated case. For example:

SELECT 
    typeof(123::varchar) AS typeof_1,
    typeof(123::VARCHAR) AS typeof_2,
    pg_typeof(123::varchar) AS pg_typeof_1,
    pg_typeof(123::VARCHAR) AS pg_typeof_2;

Result:

+----------+----------+-------------+-------------+
| typeof_1 | typeof_2 | pg_typeof_1 | pg_typeof_2 |
+----------+----------+-------------+-------------+
| VARCHAR  | VARCHAR  | varchar     | varchar     |
+----------+----------+-------------+-------------+

In this case I used a combination of uppercase and lowercase when assigning the new data type, but this didn’t change how each function output its result.

In other words:

  • typeof() output its result in uppercase.
  • pg_typeof() output its result in lowercase.

Conclusion

If you need to maintain compatibility with PostgreSQL, then use pg_typeof(). You may also want to use this if you want the result output in lowercase.

If you need to maintain compatibility with SQLite, then use typeof() (as SQLite has its own typeof() function that works identically to DuckDB’s implementation).

If you have no preference, then either function should be fine.