PostgreSQL Data Types: Complete Guide

PostgreSQL offers a rich set of data types that go far beyond the basic INTEGER and TEXT, including geometric, network, and JSON types. This article explains the most common PostgreSQL data types, how to choose the right one, and answers frequent questions about their usage.

Background: Why Data Types Matter

Data types define what kind of value a column can hold, how that value is stored, and what operations can be performed on it. Choosing the correct type ensures data integrity, improves query performance, and reduces storage waste. PostgreSQL’s type system is one of its standout features, offering over 40 built-in types, many of which are not available in other relational databases like MySQL or SQL Server.

Numeric Types

PostgreSQL provides several numeric types to handle different ranges and precision needs:

• SMALLINT – 2-byte integer, range -32,768 to 32,767.
• INTEGER (INT) – 4-byte integer, range -2,147,483,648 to 2,147,483,647.
• BIGINT – 8-byte integer, range -9.2e18 to 9.2e18.
• DECIMAL / NUMERIC – arbitrary precision, ideal for financial amounts.
• REAL – 4-byte floating point, ~6 decimal digits precision.
• DOUBLE PRECISION – 8-byte floating point, ~15 decimal digits precision.

For monetary values, always use NUMERIC to avoid rounding errors. For scientific calculations where slight imprecision is acceptable, DOUBLE PRECISION is faster.

Character and Text Types

PostgreSQL offers three character types:

• CHAR(n) – fixed-length, blank-padded. Use when the length is strictly fixed, like a country code.
• VARCHAR(n) – variable-length with a limit. Use for strings with a known maximum, like usernames.
• TEXT – variable-length without limit. Use for open-ended content like descriptions or article bodies.

There is no performance difference between VARCHAR and TEXT in PostgreSQL; both are stored the same way. The length limit in VARCHAR is a constraint, not a storage optimization. Most developers prefer TEXT for simplicity.

Date and Time Types

Handling time correctly is crucial. PostgreSQL provides:

• DATE – stores year, month, day.
• TIME – stores hour, minute, second, with optional timezone.
• TIMESTAMP – stores both date and time, with or without timezone.
• INTERVAL – represents a duration, e.g., ‘2 days 3 hours’.

Use TIMESTAMPTZ (timestamp with time zone) for global applications. It stores the time in UTC internally and converts to the session time zone for display, which avoids many timezone bugs.

Boolean and Enumerated Types

BOOLEAN stores TRUE, FALSE, or NULL. It uses 1 byte. For fixed sets of values, you can create an ENUM type:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Enums are convenient but have limitations: adding a new value requires an ALTER TYPE command, and they are not easy to reorder. For sets that might change frequently, a lookup table with a foreign key is more flexible.

JSON and JSONB

PostgreSQL is famous for its JSON support. JSON stores the exact text you insert, preserving whitespace and key order. JSONB stores a decomposed binary format, which is slower to insert but much faster to query, and it supports indexing with GIN indexes. Use JSONB for almost all cases where you need to query the JSON content. Example:

SELECT data->'name' FROM users WHERE data @> '{"age": 30}';

Arrays and Composite Types

Arrays let you store multiple values in a single column, e.g., TEXT[] or INTEGER[]. They are useful for simple lists, but if you need to query individual elements frequently, a separate table might be better. Composite types allow you to define a custom structure:

CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);

Then use it as a column type. This can be handy for nested data, but again, normalization is often preferable.

Geometric and Network Types

PostgreSQL includes geometric types like POINT, LINE, CIRCLE, and POLYGON for spatial data. For more advanced GIS work, the PostGIS extension adds full geospatial capabilities. Network types include INET for IPv4/IPv6 addresses and CIDR for network blocks. These types support special operators for range checks, e.g., ip << '192.168.1.0/24'.

How to Choose the Right Type

Consider these factors when selecting a data type:

• Range – Will the value exceed 2 billion? Use BIGINT.
• Precision – Does the value need exact decimal representation? Use NUMERIC.
• Length – Is there a natural maximum length? Use VARCHAR(n) or CHAR(n).
• Timezone – Do you need to handle users across time zones? Use TIMESTAMPTZ.
• Queryability – Will you search inside the data? Use JSONB instead of JSON.

When in doubt, choose the more flexible type (TEXT over VARCHAR, TIMESTAMPTZ over TIMESTAMP) and add constraints later if needed.

Common Mistakes and Misconceptions

One frequent mistake is using VARCHAR(255) for all strings, assuming it saves space. In PostgreSQL, VARCHAR(255) and TEXT occupy the same storage. Another misconception is that TIMESTAMP without timezone is simpler; it actually leads to incorrect times if your app deals with multiple time zones. Also, avoid using REAL or DOUBLE PRECISION for money – rounding errors will accumulate. Finally, don’t use SERIAL for auto-incrementing IDs if you need a larger range; use BIGSERIAL instead.

Frequently Asked Questions

What is the difference between VARCHAR and TEXT in PostgreSQL?

There is no performance difference; both store variable-length strings. VARCHAR(n) enforces a maximum length, while TEXT has no limit. Use TEXT unless you need to enforce a length for business reasons.

When should I use NUMERIC vs INTEGER for a price column?

Use NUMERIC for prices because it stores exact decimal values, avoiding floating-point rounding errors. INTEGER should only be used if you store cents as whole numbers, but that complicates calculations.

Is JSONB always better than JSON?

For most use cases, yes. JSONB supports indexing and is faster to query, though it is slightly slower to insert and does not preserve whitespace or key order. Use JSON only if you need to store the exact text and never query its contents.

How do I store an array of tags in PostgreSQL?

You can use a TEXT[] column. For example, CREATE TABLE posts (id INT, tags TEXT[]);. Query with WHERE 'postgres' = ANY(tags). For heavy tag filtering, a separate junction table is more efficient.

What is the best type for a timestamp with time zone?

Use TIMESTAMPTZ. It stores the time in UTC internally and converts to the session time zone on retrieval. This is the recommended approach for any application that deals with users in multiple time zones.

Can I use ENUM in PostgreSQL?

Yes, you can create an ENUM type with CREATE TYPE. However, adding a new value later requires an ALTER TYPE command and cannot be done in a transaction. A lookup table with a foreign key is often more flexible.

Related

• IBM Bob vs GitHub Copilot Business: On-Premises Data Control
• AI Boom vs. Dot-Com Crash: What Developers Should Watch
• AI Agent Harness vs Framework: What’s the Difference?