SQL INT4

What is SQL INT4?

INT4 is PostgreSQL’s 4-byte signed integer data type, fully equivalent to the standard INTEGER type.

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 INT4: Supported: PostgreSQL, Amazon Redshift, CockroachDB, YugabyteDB. Not supported natively: MySQL, MariaDB, SQL Server, Oracle, SQLite.

SQL INT4 Full Explanation

INT4 is a shorthand, PostgreSQL-specific alias for the INTEGER data type. It consumes 4 bytes of storage and can hold whole numbers from ‑2,147,483,648 to 2,147,483,647. Because INT4 is just an alias, it has identical performance characteristics, indexing behavior, and semantics as INTEGER or INT in PostgreSQL. Developers often use INT4 in table definitions, CAST expressions, and domain creation to make the chosen size explicit or to match existing codebases that rely on the alias. INT4 is not part of the ANSI SQL standard, so code portability can be affected when moving to engines other than PostgreSQL or its derivatives. Use INT4 (or INTEGER) for columns that do not need values outside the 32-bit signed range. For larger ranges use BIGINT (INT8); for smaller ranges use SMALLINT (INT2). Serial variants: SERIAL4 or INT4 GENERATED ALWAYS AS IDENTITY create auto-incrementing 4-byte integer columns. These share the same range as INT4 but automatically populate sequential values. Caveats: • Overflow raises an error. • INT4 stores only whole numbers; decimals are truncated if implicitly cast. • Cross-database migration requires mapping INT4 to INTEGER or INT.

SQL INT4 Syntax

column_name INT4
-- or explicitly casting
SELECT '123'::INT4;

SQL INT4 Parameters

Example Queries Using SQL INT4

-- 1. Create table with INT4 columns
CREATE TABLE orders (
    id INT4 PRIMARY KEY,
    quantity INT4 NOT NULL,
    price_cents INT4
);

-- 2. Cast text to INT4
SELECT '12345'::INT4 AS order_id;

-- 3. Define a domain enforcing positive INT4 values
CREATE DOMAIN positive_int4 AS INT4
    CHECK (VALUE > 0);

Expected Output Using SQL INT4

The CREATE TABLE statement returns CREATE TABLE and the table is created with INT4 columns.
The SELECT query returns a single row with integer value 12345 of type INT4.
The CREATE DOMAIN statement returns CREATE DOMAIN and registers the domain in pg_catalog.

Use Cases with SQL INT4

Storing identifiers, counters, and status flags that fit in 32-bit range.
Creating lightweight numeric columns for performance-critical tables where BIGINT is unnecessary.
Enforcing data type clarity in schemas that mix multiple integer sizes.
Building domains or composite types constrained to 4-byte integers.

Common Mistakes with SQL INT4

Assuming INT4 is universally supported – other databases may reject the keyword.
Choosing INT4 when values may exceed the 32-bit range, leading to overflow errors later.
Confusing INT4 with SERIAL4 – the latter auto-increments.
Expecting INT4 to store decimal or monetary values; it truncates fractional parts.

First Introduced In

PostgreSQL 7.0

Frequently Asked Questions

What range of values can INT4 store?

INT4 can store any whole number between ‑2,147,483,648 and 2,147,483,647.

Is INT4 portable across databases?

INT4 is PostgreSQL-specific. On other systems you should replace it with INTEGER or INT to maintain compatibility.

Does INT4 save space compared to BIGINT?

Yes. INT4 uses 4 bytes per value, while BIGINT uses 8 bytes. Choosing the smallest adequate type improves cache efficiency and I/O throughput.

How do I create an auto-incrementing INT4 column?

Declare the column as SERIAL4 or INT4 GENERATED ALWAYS AS IDENTITY; PostgreSQL will manage a sequence and populate new values automatically.