Reveal the True Type of Any Value using ClickHouse toType function

  

ClickHouse is a type-strict database, it requires explicit types when defining schemas and expressions. Sometimes, especially when dealing with constants, expressions, or implicit type conversions, you might want to inspect the actual type that ClickHouse has inferred. This is where the toTypeName function helps.

 

Syntax

toTypeName(value_or_column)

   

toTypeName function take any expression or column and return a String representing the data type (e.g., 'UInt8', 'String', 'Nullable(Int32)', etc.)

 

Example 1: Detecting the Type of a Constant

SELECT 
42 AS value,
toTypeName(42) AS type;

krishna :) SELECT 
42 AS value,
toTypeName(42) AS type;

SELECT
    42 AS value,
    toTypeName(42) AS type

Query id: 109c4f0e-0f2c-4d23-8336-2f6251733a23

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.5.1.

   ┌─value─┬─type──┐
1. │    42 │ UInt8 │
   └───────┴───────┘

1 row in set. Elapsed: 0.002 sec.

   

Example 2: Larger Integer Literal

 

SELECT 
123456789012 AS value,
toTypeName(123456789012) AS type;

krishna :) SELECT 
123456789012 AS value,
toTypeName(123456789012) AS type;

SELECT
    123456789012 AS value,
    toTypeName(123456789012) AS type

Query id: fe765072-1f25-4751-b394-e4cb60e83f72

   ┌────────value─┬─type───┐
1. │ 123456789012 │ UInt64 │
   └──────────────┴────────┘

1 row in set. Elapsed: 0.002 sec.

   

Example 3: Type of a column.

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.sensor_data (
    id UInt32,
    temperature Float32
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO demo_db.sensor_data VALUES (1, 34.5);

SELECT 
    id,
    toTypeName(id) AS id_type,
    toTypeName(temperature) AS temp_type
FROM demo_db.sensor_data
LIMIT 1;

krishna :) SELECT 
    id,
    toTypeName(id) AS id_type,
    toTypeName(temperature) AS temp_type
FROM demo_db.sensor_data
LIMIT 1;

SELECT
    id,
    toTypeName(id) AS id_type,
    toTypeName(temperature) AS temp_type
FROM demo_db.sensor_data
LIMIT 1

Query id: f82672b1-f503-4427-87cd-d4a59dab2efb

   ┌─id─┬─id_type─┬─temp_type─┐
1. │  1 │ UInt32  │ Float32   │
   └────┴─────────┴───────────┘

1 row in set. Elapsed: 0.004 sec.

   

Example 4: Nullable types

 

SELECT 
NULL AS n,
toTypeName(NULL) AS type;

krishna :) SELECT 
NULL AS n,
toTypeName(NULL) AS type;

SELECT
    NULL AS n,
    toTypeName(NULL) AS type

Query id: c075f43a-6a24-42d2-83ea-c26d3f9b4a1c

   ┌─n────┬─type──────────────┐
1. │ ᴺᵁᴸᴸ │ Nullable(Nothing) │
   └──────┴───────────────────┘

1 row in set. Elapsed: 0.002 sec.

   

Example 5: Expression Result Types

 

SELECT 
10.5 + 3.5 AS result,
toTypeName(10.5 + 3.5) AS type;

krishna :) SELECT 
10.5 + 3.5 AS result,
toTypeName(10.5 + 3.5) AS type;

SELECT
    10.5 + 3.5 AS result,
    toTypeName(10.5 + 3.5) AS type

Query id: 928dcb66-88b2-4a2a-8c4d-839a15dacb8a

   ┌─result─┬─type────┐
1. │     14 │ Float64 │
   └────────┴─────────┘

1 row in set. Elapsed: 0.003 sec.

   

Example 6: Nullable Columns in Tables

 

CREATE TABLE demo_db.user_logins (
    user_id UInt64,
    last_login Nullable(DateTime)
) ENGINE = MergeTree()
ORDER BY user_id;

INSERT INTO demo_db.user_logins VALUES (1, NULL);

SELECT 
    toTypeName(last_login) AS login_type 
FROM demo_db.user_logins 
LIMIT 1;

krishna :) SELECT 
    toTypeName(last_login) AS login_type 
FROM demo_db.user_logins 
LIMIT 1;

SELECT toTypeName(last_login) AS login_type
FROM demo_db.user_logins
LIMIT 1

Query id: c1e8636d-2155-4105-b8dc-962b92c32b14

   ┌─login_type─────────┐
1. │ Nullable(DateTime) │
   └────────────────────┘

1 row in set. Elapsed: 0.002 sec.

Notice nullable wrapping around base types.

Previous                                                    Next                                                    Home