PostgreSQL Data Types Explained with Examples

Data types define what kind of data can be stored in a column.
Choosing the correct data type is very important for performance, storage, and data accuracy.

In this article, you will learn:

• What data types are in PostgreSQL
• Common PostgreSQL data types
• When to use each data type
• Examples for every data type

This guide is written for beginners.

---

What Are Data Types in PostgreSQL?

A data type tells PostgreSQL:

• What kind of value a column can hold
• How much space it needs
• How the data can be processed

Example:

• Numbers → numeric data types
• Text → character data types
• Dates → date and time data types

---

Categories of PostgreSQL Data Types

PostgreSQL data types are grouped into these main categories:

1. Numeric Data Types
2. Character (String) Data Types
3. Date and Time Data Types
4. Boolean Data Type
5. UUID Data Type
6. JSON Data Types

---

1. Numeric Data Types

Numeric data types are used to store numbers.

INTEGER

Stores whole numbers.

age INTEGER

Example values:

• 1
• 100
• -50

---

BIGINT

Stores very large whole numbers.

total_users BIGINT

Use when values can exceed integer limits.

---

SERIAL

Automatically generates increasing numbers.

id SERIAL PRIMARY KEY

Commonly used for auto‑increment IDs.

---

NUMERIC

Stores exact numbers with decimals.

price NUMERIC(10,2)

Example:

• 1999.99
• 100.50

Best for financial data.

---

2. Character (String) Data Types

Used to store text data.

---

TEXT

Stores unlimited length text.

description TEXT

Use TEXT when length is unknown or large.

---

VARCHAR

Stores text with a maximum length.

username VARCHAR(50)

Example:

• "ravi123"

---

CHAR

Fixed‑length text.

country_code CHAR(2)

Example:

• "IN"
• "US"

---

3. Date and Time Data Types

Used to store date and time values.

---

DATE

Stores only the date.

birth_date DATE

Example:

• 2024-01-15

---

TIME

Stores only time.

login_time TIME

Example:

• 10:30:00

---

TIMESTAMP

Stores date and time.

created_at TIMESTAMP

Example:

• 2024-01-15 10:30:00

---

TIMESTAMP WITH TIME ZONE

Stores date and time with timezone information.

created_at TIMESTAMPTZ

Recommended for global applications.

---

4. Boolean Data Type

Stores true or false values.

is_active BOOLEAN

Valid values:

• TRUE
• FALSE

---

5. UUID Data Type

Used to store unique identifiers.

user_id UUID

Example:

• 550e8400-e29b-41d4-a716-446655440000

Often used in distributed systems.

---

6. JSON and JSONB Data Types

Used to store JSON data.

---

JSON

Stores JSON in text format.

profile JSON

JSONB

Stores JSON in binary format (faster).

profile JSONB

Use JSONB for better performance.

---

Example Table Using Multiple Data Types

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

email TEXT,

age INTEGER,

is_active BOOLEAN,

created_at TIMESTAMP,

profile JSONB

);

Choosing the Right Data Type

General rules:

• Use INTEGER for numbers
• Use TEXT for strings
• Use VARCHAR when size matters
• Use NUMERIC for money
• Use TIMESTAMPTZ for timestamps
• Use JSONB for JSON data

Correct data types improve:

• Performance
• Storage efficiency
• Data integrity

---

Summary

• PostgreSQL supports many data types
• Data types define how data is stored
• Choosing correct data types is crucial
• Common types include INTEGER, TEXT, DATE, TIMESTAMP, BOOLEAN, JSONB

You now have a strong understanding of PostgreSQL data types.

---

What’s Next?

Next, we will learn about constraints in PostgreSQL.

Next article:
PostgreSQL Constraints Explained (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL)