SQL INT2

What is INT2 in SQL?

INT2 stores a signed 2-byte (16-bit) integer value.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INT2: PostgreSQL: Yes Amazon Redshift: Yes (alias SMALLINT) MySQL/MariaDB: No (use SMALLINT) SQL Server: No (use SMALLINT) Oracle: No (use NUMBER(5)) SQLite: Accepts but treats as NUMERIC affinity

SQL INT2 Full Explanation

INT2 is a fixed-precision, signed 16-bit integer data type. It can hold whole-number values from -32,768 to 32,767. Although INT2 is functionally equivalent to SMALLINT in the SQL standard, the literal keyword INT2 is primarily recognized by PostgreSQL and a few engines with PostgreSQL compatibility layers. Using INT2 helps conserve storage when the range of values fits inside two bytes, and can slightly improve cache efficiency on very wide tables. Arithmetic operations on INT2 promote values to a wider integer type (INT4) during computation in PostgreSQL, so performance differences are usually negligible. Be mindful of implicit casts: inserting a number outside the valid range raises an error, and comparing INT2 with larger numeric types can trigger implicit up-casting.

SQL INT2 Syntax

-- Column definition
CREATE TABLE table_name (
    col_small  INT2
);

-- Literal cast
SELECT CAST(42 AS INT2);

-- Using :: cast operator (PostgreSQL)
SELECT 12345::INT2;

SQL INT2 Parameters

Example Queries Using SQL INT2

-- 1. Create table with INT2 column
CREATE TABLE sensor_readings (
    id          SERIAL PRIMARY KEY,
    status_code INT2 NOT NULL
);

-- 2. Insert valid values
INSERT INTO sensor_readings (status_code) VALUES (200), (404);

-- 3. Attempt to insert out-of-range value (fails)
INSERT INTO sensor_readings (status_code) VALUES (70000);  -- error

-- 4. Cast larger integer to INT2
SELECT 15000::INT2 AS small_value;

-- 5. Check storage size
SELECT pg_column_size(15000::INT2) AS bytes_used;

Expected Output Using SQL INT2

Table is created with a 2-byte integer column.
Two rows are inserted successfully.
Database returns an error: value "70000" is out of range for type int2.
Query returns the integer 15000 typed as INT2.
pg_column_size shows 2, proving two-byte storage.

Use Cases with SQL INT2

Storing narrow codes such as HTTP status, age, rating, or enumeration IDs that never exceed 32,767.
Reducing disk footprint of massive fact tables where billions of rows store small integers.
Optimizing memory usage for in-memory analytics on integer dimensions.
Representing Boolean-like flags that need three states (-1, 0, 1) instead of true/false.

Common Mistakes with SQL INT2

Assuming INT2 and SMALLINT differ in PostgreSQL—they are synonyms.
Forgetting the limited range and causing out-of-range errors on insert.
Expecting space savings in engines that internally up-cast INT2 to 4 bytes (some ORMs may do this).
Using INT2 for high-volume arithmetic computations, which may promote to larger types and negate performance gains.

First Introduced In

PostgreSQL 7.0

Frequently Asked Questions

What is the difference between INT2 and SMALLINT?

PostgreSQL treats INT2 and SMALLINT as exact synonyms. Other databases only recognize SMALLINT.

How much space does an INT2 value consume?

Each INT2 value occupies exactly 2 bytes of on-disk storage in PostgreSQL.

Can I change an existing INT column to INT2?

Yes - use ALTER TABLE ... ALTER COLUMN ... TYPE INT2, but first ensure no values exceed the INT2 range.

Does using INT2 improve query speed?

Disk I/O can improve slightly for wide, read-heavy tables because less data is moved. CPU performance is usually unchanged.