Skip to main content

Understanding Postgresql data types

PostgreSQL is a powerful, open-source relational database system that uses and extends the SQL language. It offers a variety of built-in data types. Understanding these data types can help you build robust and high-performing databases. In this article, we will explore the various data types offered by PostgreSQL.

Numeric Types​

Numeric types are used to store the numerical values. PostgreSQL provides two distinct types of numeric data types.

  • Integer Types : These are used to store whole numbers. PostgreSQL offers smallint, integer, and bigint for this purpose.

    • smallint: This is used for storing 2 bytes of integers, with a range of -32768 to 32767.
    • integer: This is used for storing 4 bytes of integers, with a range of -2147483648 to 2147483647.
    • bigint: This is used for storing 8 bytes of integers, with a range of -9223372036854775808 to 9223372036854775807.

  • Floating-Point Types : These are used to store decimal numbers. PostgreSQL offers real and double precision.

    • real: This is used for storing 4 bytes of floating-point numbers, with a precision of 6 decimal digits.
    • double precision: This is used for storing 8 bytes of floating-point numbers, with a precision of 15 decimal digits.

Character Types​

Character types are used to store text values.

  • character(n) or char(n): This is used for storing fixed-length character data. The maximum allowable size is 10485760.
  • varchar(n): This is used for storing variable-length character data. The maximum allowable size is also 10485760.
  • text: This is used for storing variable unlimited length character data.

Boolean Types​

Boolean types are used to store true or false values.

  • boolean or bool: This type stores either true, false or null.

Date/Time Types​

PostgreSQL offers a variety of date/time types for dealing with dates, times, and intervals.

  • date: This is used for storing date values.
  • time: This is used for storing time of day values.
  • timestamp: This is used for storing date and time values.
  • interval: This is used for storing periods of time.

Miscellaneous Types​

There are also other types provided by PostgreSQL such as:

  • ARRAY: This is used for storing an ordered list of a certain data type.
  • hstore: This extension is used for storing key-value pairs.
  • JSON: This is used for storing JSON formatted data.
  • UUID: This is used for storing Universally Unique Identifiers.
  • Geometric Types: These are used for storing 2D spatial objects such as points, lines, and polygons.

In this article, we have covered the basic data types provided by PostgreSQL, but there are many more that can be explored. Understanding these data types is fundamental to leveraging PostgreSQL's full potential. The choice of data type can affect the functionality, storage, performance, and range of your data.