PostgreSQL – Data Types

By /
Datatypes In Postgresql

PostgreSQL is a powerful, open-source relational database management system that supports a wide range of data types. In this article, we will discuss some of the most commonly used data types in PostgreSQL and how they can be used effectively in your database.

Introduction to PostgreSQL Data types

Postgresql, you can say, is an ocean of data types. There are a bunch of data types for almost every type of data that you would come across while building an application. However, choosing the correct data type is the most important thing and is totally up to you.

Here we will see all the data types that PostgreSQL supports such as Numeric types, Character types, Binary types, Date-Time types, etc. Note that, each type itself can have an individual tutorial on it. Therefore, we will be covering only the overview of available data types without any details explanation and examples.

PostgreSQL Data Types

Following is the list of available data types in PostgreSQL. Later in this tutorial, we will see all the data types available under these types.

  • Numeric
  • Monetary
  • Character
  • Binary
  • Date-Time
  • Boolean
  • Enum
  • Geometric
  • Network Address
  • Text-Search
  • UUID
  • XML
  • JSON
  • Arrays
  • Composite
  • Range
  • Domain

Now let’s see information about each data type.

Numeric Data Types

The numeric data type in PostgreSQL has several subtypes that are used for specific cases:

  • smallint: a small integer that requires 2 bytes of storage and can store values between -32,768 and 32,767.
  • integer: a standard integer that requires 4 bytes of storage and can store values between -2,147,483,648 and 2,147,483,647.
  • bigint: a large integer that requires 8 bytes of storage and can store values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
  • decimal: a precise decimal number that allows you to specify the exact number of digits in the number. It uses a variable amount of storage depending on the precision and scale specified.
  • numeric: same as a decimal is an arbitrary precision number, It can be used when you need to store extremely large or precise numbers.

In addition to these subtypes, PostgreSQL also supports several other numeric data types, such as real, double precision, and money which are used for floating-point numbers.

Monetary Data Type

The money data type in PostgreSQL is used to store currency values. It is a fixed-precision decimal data type with a scale of 2, which means that it can store decimal numbers with up to two digits to the right of the decimal point.

The money data type is implemented as a 64-bit integer and the currency symbol is not stored in the column, so it is important to store the currency symbol separately if you need to display it.

Character Data Types

In PostgreSQL, there are several character data types that can be used to store strings of characters. The most commonly used character data types are:

  • character (or char): a fixed-length string of characters. The maximum length of the string is specified when the column is created. If you try to insert a string with more characters than the maximum length, it will be truncated to the maximum length.
  • character varying (or varchar): a variable-length string of characters. The maximum length of the string is specified when the column is created, but the actual length of the stored string can be less than or equal to the maximum length.
  • text: a variable-length string of characters. It does not have a maximum length and can store very large strings.

Date-Time Data Types

PostgreSQL provides several data types for storing date and time values. These include:

  • date: used to store a date (year, month, and day) value in the format ‘YYYY-MM-DD’.
  • time: used to store a time (hours, minutes, seconds, and microseconds) value in the format ‘HH:MM:SS.microseconds’.
  • timestamp: used to store a timestamp (date and time) value in the format ‘YYYY-MM-DD HH:MM:SS.microseconds’.
  • timestamp with time zone: used to store a timestamp with time zone information. The time zone is stored as an offset from UTC.
  • interval: used to store a duration of time. It can represent a time period or the time between two timestamps.

Boolean Data Type

The boolean data type in PostgreSQL is used to store true or false values. It is a simple data type that can be used to store logical values, such as the status of a task or the result of a query.

Enum Data Types

PostgreSQL provides several data types for storing geometric data, such as points, lines, and polygons. These types are known as the geometry types and they are based on the Open Geospatial Consortium (OGC) Simple Features Specification.

The most commonly used geometry types in PostgreSQL are:

  • point: used to store a single point in 2D or 3D space. It is represented by a pair of coordinates (x, y) or (x, y, z).
  • line: used to store a line segment in 2D or 3D space. It is represented by a series of points.
  • polygon: used to store a closed shape in 2D space. It is represented by a series of points that form a closed loop.

In addition to these types, PostgreSQL also provides several other geometry types, such as box, path, circle, and lseg, among others.

Network Address Data Types

In PostgreSQL, there are several types of network addresses that can be used. These include:

  • inet: This type is used for IPv4 addresses, and can also store the address’s subnet mask.
  • cidr: This type is similar to inet, but is used for IPv4 and IPv6 addresses and their subnet masks. It can also be used for Classless Inter-Domain Routing (CIDR) blocks.
  • macaddr: This type is used for Media Access Control (MAC) addresses.
  • macaddr8: This type is used for MAC addresses with 64 bits.

JSON Data Types

In PostgreSQL, there are several types for storing and manipulating JSON data. These include:

  • json: This type stores JSON data as text. It can be used to store JSON data that conforms to the JSON specification.
  • jsonb: This type is similar to json, but stores JSON data in a binary format. This allows for more efficient storage and processing of JSON data, but also means that the data must be converted to and from text when interacting with it. jsonb also supports GIN and GIST indexes, which can speed up the querying of JSONb data.

Array

In PostgreSQL, an array is a data type that allows you to store and manipulate a collection of elements, all of the same data type. Elements can be of any data type that PostgreSQL supports, including text, integers, and floating-point numbers. Arrays can be one-dimensional (also known as “vectors”) or multi-dimensional (also known as “matrices”). They are often used to store data that can be represented as a list or table.

Conclusion

In this article, we have seen some of the most important data types and their information. Note that, there are so many other data types available in PostgreSQL that you can use, but they are not as important/”widely used” as the ones shown here. You can refer to the official documentation of PostgreSQL if you want to read more about PostgreSQL datatypes.

References

PostgreSQL official documentation on data types.