PostgreSQL Data Types

PostgreSQL Data Types

PostgreSQL provides a rich set of data types to store different kinds of data efficiently. Below is a categorized list of the most commonly used PostgreSQL data types.

1. Numeric Data Types

Used for storing numbers (integers, decimals, floating points, etc.).

Data Type	Alias	Storage Size	Description
SMALLINT	int2	2 bytes	Stores small integers (-32,768 to 32,767)
INTEGER	int4	4 bytes	Stores standard integers (-2,147,483,648 to 2,147,483,647)
BIGINT	int8	8 bytes	Stores large integers (-9 quintillion to +9 quintillion)
DECIMAL(p, s)	NUMERIC(p, s)	Variable	Stores fixed precision decimals (e.g., DECIMAL(10,2))
REAL	float4	4 bytes	Stores single-precision floating-point numbers
DOUBLE PRECISION	float8	8 bytes	Stores double-precision floating-point numbers
SERIAL	int4	4 bytes	Auto-incrementing integer (used for primary keys)
BIGSERIAL	int8	8 bytes	Auto-incrementing big integer

💡 Use NUMERIC for precise decimal values (e.g., for currency calculations).

2. Character Data Types

Used to store text-based data.

Data Type	Storage Size	Description
CHAR(n)	n bytes	Fixed-length character string (e.g., CHAR(10))
VARCHAR(n)	Variable	Variable-length character string (e.g., VARCHAR(255))
TEXT	Variable	Unlimited-length string (slower indexing than VARCHAR)

💡 Use TEXT for large text data and VARCHAR(n) for performance optimization.

3. Boolean Data Type

Used for storing TRUE or FALSE values.

Data Type	Storage Size	Description
BOOLEAN	1 byte	Stores TRUE, FALSE, or NULL

💡 Use this for flags (e.g., is_active, is_verified).

4. Date & Time Data Types

Used to store dates and timestamps.

Data Type	Storage Size	Description
DATE	4 bytes	Stores date (YYYY-MM-DD)
TIME	8 bytes	Stores time (HH:MI:SS)
TIMESTAMP	8 bytes	Stores date and time (without time zone)
TIMESTAMP WITH TIME ZONE	8 bytes	Stores date and time (with time zone support)
INTERVAL	12 bytes	Stores time intervals (e.g., 1 day 2 hours)

💡 Use TIMESTAMP WITH TIME ZONE for applications dealing with multiple time zones.

5. JSON Data Types

Used to store structured data in JSON format.

Data Type	Storage Size	Description
JSON	Variable	Stores unstructured JSON data (slow search)
JSONB	Variable	Stores binary JSON data (faster search & indexing)

💡 Use JSONB for better performance in queries and indexing.

6. UUID (Universally Unique Identifier)

Used to store unique identifiers.

Data Type	Storage Size	Description
UUID	16 bytes	Stores universally unique identifiers (e.g., 550e8400-e29b-41d4-a716-446655440000)

💡 Use UUID for unique primary keys instead of SERIAL in distributed systems.

7. Array Data Type

Used to store arrays of a specific data type.

Data Type	Example
INTEGER[]	{1,2,3,4}
TEXT[]	{'apple', 'banana', 'cherry'}
BOOLEAN[]	{TRUE, FALSE, TRUE}

💡 Use arrays when a column needs to store multiple values of the same type.

8. Network Address Data Types

Used for storing network-related data.

Data Type	Storage Size	Description
INET	7 or 19 bytes	Stores IPv4 or IPv6 addresses
CIDR	7 or 19 bytes	Stores network blocks (e.g., 192.168.0.0/24)
MACADDR	6 bytes	Stores MAC addresses (e.g., 08:00:2b:01:02:03)

💡 Use INET for storing IP addresses.

9. Geometric Data Types

Used for storing geometric shapes.

Data Type	Description
POINT	A point in 2D space (e.g., (1,2))
LINE	A line in 2D space
LSEG	A line segment
BOX	A rectangular box
CIRCLE	A circle

💡 Useful for GIS (Geographic Information System) applications.

10. XML Data Type

Used to store XML data.

Data Type	Storage Size	Description
XML	Variable	Stores well-formed XML data

💡 Use XML if you need to store and query XML-based data.

11. Full-Text Search Data Types

Used for efficient text searching.

Data Type	Storage Size	Description
TSVECTOR	Variable	Stores a document for full-text search
TSQUERY	Variable	Stores a search query

💡 Use these for advanced text search functionalities.

12. Custom Data Types

PostgreSQL allows users to define custom data types.

Example:

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

Choosing the Right Data Type

1. For large text fields → Use TEXT
2. For small strings (e.g., names, emails) → Use VARCHAR(n)
3. For precise decimal values (e.g., prices) → Use NUMERIC
4. For timestamps with time zones → Use TIMESTAMP WITH TIME ZONE
5. For unique keys → Use UUID instead of SERIAL
6. For JSON data → Use JSONB for better performance
7. For storing lists → Use ARRAY[]

Final Thoughts

Choosing the right data type in PostgreSQL improves performance, storage efficiency, and query speed. If you need a specific recommendation based on your use case, let me know! 🚀