PostgreSQL Data Types

---

PostgreSQL supports a wide range of data types to accommodate various types of data, including numeric, string, date and time, boolean, geometric, network address, and custom user-defined types.

PostgreSQL data types:

1. Numeric Types:

   • integer: 4-byte integer.
   • bigint: 8-byte integer.
   • numeric(precision, scale): Arbitrary precision number.
   • real: 4-byte floating-point number.
   • double precision: 8-byte floating-point number.

2. Character Types:

   • character varying(n): Variable-length character string with a maximum length of n.
   • text: Variable-length character string.
   • character(n): Fixed-length character string of length n.

3. Binary Data Types:

   • bytea: Binary data, used for storing binary files or data.

4. Date and Time Types:

   • timestamp: Date and time with timezone.
   • date: Date without time.
   • time: Time without date.
   • interval: Time interval.

5. Boolean Type:

   • boolean: Represents true or false values.

6. Geometric Types:

   • point: Represents a point in 2D space.
   • line: Represents an infinite line in 2D space.
   • polygon: Represents a closed 2D shape.
   • circle: Represents a circle in 2D space.

7. Network Address Types:

   • inet: Represents an IPv4 or IPv6 host address.
   • cidr: Represents an IPv4 or IPv6 network.
   • macaddr: Represents a MAC (Media Access Control) address.

8. Bit Strings:

   • bit(n): Fixed-length bit strings.
   • bit varying(n): Variable-length bit strings.

9. Enum Type:

   • enum: User-defined enumeration type.

10. UUID Type:

    • uuid: Universally unique identifier.

11. Text Search Types:

    • tsvector: Text search document.
    • tsquery: Text search query.

12. JSON Types:

    • json: Stores JSON data.
    • jsonb: Stores binary JSON data (more efficient for searching and indexing).

13. Array Types:

    • integer[]: An array of integers.
    • text[]: An array of text strings.

14. Composite Types:

    • User-defined types that can contain multiple fields of different data types.

15. Domain Types:

    • User-defined types based on existing types, allowing for additional constraints or validation.

16. Custom User-Defined Types:

    • Users can define their own data types in PostgreSQL.

These are just some of the most commonly used data types in PostgreSQL.

PostgreSQL also allows you to create custom types to suit your specific needs. When designing a database schema, choosing the appropriate data types is crucial for data integrity and query performance.

Enroll Now

• SQL
• DBMS