What are the different data types supported by PostgreSQL?

PostgreSQL offers a rich set of data types, which is one of its most popular features as an enterprise-grade database system. Below, I'll outline key data types and provide usage examples.

Numerical Types

1. Integer Types:

   • SMALLINT: Used for storing smaller integers, ranging from -32768 to 32767.
   • INTEGER: Used for storing standard-sized integers, ranging from -2147483648 to 2147483647. For example, user age or a counter.
   • BIGINT: Used for storing large integers, ranging from -9223372036854775808 to 9223372036854775807. Suitable for large-scale statistics, such as user counts on social media platforms.
   • SERIAL: Auto-incrementing integer, commonly used for automatically generating unique row identifiers in tables.

2. Exact Numeric Types:

   • NUMERIC and DECIMAL: These types store exact numeric values with specified precision (total digits) and scale (digits after the decimal point). For example, financial transaction amounts.

3. Floating Point Types:

   • REAL and DOUBLE PRECISION: Used for storing floating-point numbers, with REAL being single-precision and DOUBLE PRECISION being double-precision. Used for scientific calculations requiring approximate values.

Text Types

1. CHAR(n): Fixed-length string. If the string length is less than n, it is padded with spaces.
2. VARCHAR(n): Variable-length string, up to n characters. Suitable for storing variable-length data, such as user names.
3. TEXT: Variable-length string with no length limit. Ideal for storing large text, such as article content or user comments.

Date and Time Types

• DATE: Stores only dates.
• TIME: Stores only times.
• TIMESTAMP: Stores both date and time. Commonly used for recording specific event times, such as log entries.
• INTERVAL: Stores time intervals.

Boolean Types

• BOOLEAN: Stores true (TRUE) or false (FALSE). For example, user subscription status or yes/no options.

Enum Types

• ENUM: Custom type restricting possible values for a field. For example, create an ENUM type named mood with options like happy, sad, neutral.

JSON Types

• JSON and JSONB: Used for storing JSON data. JSONB is in binary format, offering faster read/write performance and index support.

Array Types

• PostgreSQL supports array data types, which can store arrays of basic types, such as integer or text arrays.

Network Address Types

• Stores IP addresses and MAC addresses, among other network-related data.

Geometric and Geographic Data Types

• Such as POINT, LINE, CIRCLE, used for storing and querying geographic spatial data.

The comprehensive support for various data types makes PostgreSQL highly suitable for handling diverse data requirements, from traditional business data to modern JSON documents and geographic spatial data.