PostgreSQL – Data Types

In PostgreSQL, data types define the kind of data that can be stored in a table’s column. PostgreSQL supports a wide range of built-in data types, which can be broadly categorized as follows:

1. Numeric Types

• integer (int): Whole numbers, 4 bytes, range from -2,147,483,648 to 2,147,483,647.
• bigint (bigserial): Large whole numbers, 8 bytes, range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• smallint (smallserial): Small whole numbers, 2 bytes, range from -32,768 to 32,767.
• serial: Auto-incrementing integer, equivalent to integer but automatically incremented.
• decimal (numeric): Exact numeric value with arbitrary precision (e.g., 10.123456).
• real (float4): Single-precision floating point number (4 bytes).
• double precision (float8): Double-precision floating point number (8 bytes).
• money: Currency value, used for financial data (includes currency symbol).

2. Character Types

• char(n): Fixed-length string with a specified length (n). Padding is done with spaces.
• varchar(n): Variable-length string with a maximum length (n).
• text: Variable-length string without a specific limit.

3. Date/Time Types

• date: Stores a date (YYYY-MM-DD).
• time [without time zone]: Stores a time of day (HH:MM:SS).
• time with time zone: Time of day with timezone information.
• timestamp [without time zone]: Stores both date and time (YYYY-MM-DD HH:MM:SS).
• timestamp with time zone: Timestamp with timezone information.
• interval: Represents a time span, e.g., ‘1 year 2 months 3 days’.

4. Boolean Type

• boolean: Can store true, false, or null.

5. Binary Data Types

• bytea: Stores raw binary data, e.g., images or files.

6. UUID (Universally Unique Identifier)

• uuid: A 128-bit value used for identifying data uniquely.

7. Array Types

• PostgreSQL supports arrays, where you can define a column as an array of any data type (e.g., integer[], text[]).

8. JSON Types

• json: Stores JSON (JavaScript Object Notation) data as text.
• jsonb: Stores JSON data in a binary format, offering faster querying and indexing.

9. Network Address Types

• cidr: IP network address (IPv4 or IPv6).
• inet: Stores a single IP address with an optional netmask.
• macaddr: MAC address.

10. Geometric Types

• point: A point in a 2D plane (x, y).
• line: Infinite line in a 2D plane.
• lseg: Line segment between two points.
• box: A rectangle in a 2D plane.
• path: A series of connected line segments.
• polygon: A closed shape with multiple sides.
• circle: A circle with a center and radius.

11. Range Types

• PostgreSQL supports range types for data types like integer, date, timestamp, etc. They are used to store ranges of values, such as a range of dates (int4range, tsrange).

12. Other Types

• enum: Stores an enumerated set of values (e.g., ‘small’, ‘medium’, ‘large’).
• composite types: Custom types created from other data types (used to represent structured data).
• tsvector: Text search vector, used for full-text search.
• tsquery: Text search query, used in conjunction with tsvector for searching.

13. Special Types

• hstore: Key-value pairs stored as a simple hash table.
• xml: Stores XML data.
• citext: Case-insensitive text.
• pg_lsn: Log sequence number used internally for transaction management.

These data types allow PostgreSQL to handle a variety of data efficiently and are highly extensible with user-defined types. Let me know if you need more details on any specific type or how to use them!