Understanding Integer Types in Apache ClickHouse: Signed, Unsigned, and Beyond 64-bit

  

ClickHouse is a powerful column-oriented OLAP database known for its performance and support for complex analytical queries. To handle various numeric requirements efficiently, ClickHouse supports a rich set of integer types, including both signed and unsigned integers and even beyond the typical 64-bit range found in most databases.

 

Following table summarizes the integer types supported in ClickHouse.

 

Type	Bits	Signed	Range
Int8	8	Yes	-128 to 127
UInt8	8	No	0 to 255
Int16	16	Yes	-32,768 to 32,767
UInt16	16	No	0 to 65,535
Int32	32	Yes	~-2.1B to ~2.1B
UInt32	32	No	0 to ~4.2B
Int64	64	Yes	-9.2e18 to 9.2e18
UInt64	64	No	0 to 1.8e19
Int128	128	Yes	Very large
UInt128	128	No	Even larger
Int256	256	Yes	Astronomically large
UInt256	256	No	Colossal

 

Most traditional RDBMSs stop at 64-bit integers. ClickHouse's support for 128- and 256-bit integers makes it suitable for domains like cryptography, blockchain, and large financial calculations.

 

When to Use Each?

·      Int8/UInt8: Useful for storing small values like Boolean flags or tiny counters.

·      Int16/UInt16: For small numeric codes like status codes, country IDs, or port numbers.

·      Int32/UInt32: Commonly used for general-purpose integers such as user IDs.

·      Int64/UInt64: Suitable for timestamps, monetary values, and large counters.

·      Int128/Int256: For cryptographic hashes, blockchain addresses, or extremely large datasets.

 

Follow below step-by-step procedure to understand numeric data types.

 

Step 1: Create demo_db database.

CREATE DATABASE IF NOT EXISTS demo_db;

   

Step 2: Create example_integers table.

 

CREATE TABLE demo_db.example_integers
(
    user_id UInt32,
    age UInt8,
    balance Int64,
    is_active UInt8,
    hash_value Int256
) ENGINE = MergeTree()
ORDER BY user_id;

   

Step 3: Insert some data into example_integers table.

 

INSERT INTO demo_db.example_integers VALUES
(1001, 27, 123456789012, 1, 1234567890123456789012345678901234567890);

   

Step 4: Print the records of example_integers table.

 

krishna :) SELECT * FROM demo_db.example_integers;

SELECT *
FROM demo_db.example_integers

Query id: 93224ab6-f0fe-456f-9d5d-852df7a5756e

   ┌─user_id─┬─age─┬──────balance─┬─is_active─┬───────────────────────────────hash_value─┐
1. │    1001 │  27 │ 123456789012 │         1 │ 1234567890123456789012345678901234567890 │
   └─────────┴─────┴──────────────┴───────────┴──────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

   

Casting large integers to strings can help avoid display/formatting issues in clients that don’t support 128/256-bit integers natively.

 

SELECT 
    user_id,
    age,
    balance,
    is_active,
    CAST(hash_value AS String) AS hash_str
FROM demo_db.example_integers;

krishna :) SELECT 
    user_id,
    age,
    balance,
    is_active,
    CAST(hash_value AS String) AS hash_str
FROM demo_db.example_integers;

SELECT
    user_id,
    age,
    balance,
    is_active,
    CAST(hash_value, 'String') AS hash_str
FROM demo_db.example_integers

Query id: dde156fe-46fc-4f10-a7bf-66ba6a167783

   ┌─user_id─┬─age─┬──────balance─┬─is_active─┬─hash_str─────────────────────────────────┐
1. │    1001 │  27 │ 123456789012 │         1 │ 1234567890123456789012345678901234567890 │
   └─────────┴─────┴──────────────┴───────────┴──────────────────────────────────────────┘

1 row in set. Elapsed: 0.004 sec.

   

In Summary,

·      Signed types (IntX) allow both positive and negative values.

·      Unsigned types (UIntX) only allow non-negative values but offer double the upper limit.

·      Prefer UInt when you know the value will never be negative.

·      Use Int256 and UInt256 cautiously; while powerful, they use more storage and are slower to process.

·      Index columns with fixed types to help ClickHouse optimize compression and query speed.

·      Use Decimal types instead of large integers for fixed-precision monetary calculations.

 

 

Previous                                                    Next                                                    Home