Exploring the various Postgres data types

In this article, we will cover the various Postgres data types and how they are used.

Relational databases organize data into tables, which are made up of rows and columns. Each table has one or more columns and each column has a specific assigned data type. These data types define the data format and their range of values. A relational database ensures that valid values are stored in a column by specifying its data type. For example, you cannot insert strings of data in a column having an integer data type (more on this to come). Instead, database professionals must use the appropriate data type and length. The rule of thumb is to choose a data type for your table column that closely matches the column’s values.

PostgreSQL supports a wide range of data types for various data requirements. Let’s explore the different Postgres data types, their range and supported values.

Postgres data types for values containing numbers

Postgres provides the following data types for different scenarios.

Integers

In the integer data type, we can store numbers without any decimal values. You can store both positive and negative values.

You should use these Postgres data types based on their ranges. For example, you should use the smallint data type for a table with a limited number of rows. Postgres displays an error – “Integer out of range,” if you try to insert a value higher than its highest possible value in the integer column.

Postgres supports pseudo data types for generating autoincrement values. For example, if the last used integer in a serial column is 100, the next record gets an autoincrement value of 101.

These are pseudo data types because they use integer, smallint or bigint data types in the background. For example, in the below CREATE TABLE statement, I defined a column ID with data type smallserial.

This automatically creates a sequence and function for generating auto incremental values.

If we generate the table script, it uses the smallint data type while we specified smallserial data type.

Arbitrary precision numbers

When it comes to arbitrary precision numbers, the type numeric can store virtually an unlimited number of digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where this type of precision is required.

Syntax: numeric(precision and scale)

• Precision defines the maximum number of total digits.
• Scale defines the number of digits after the decimal point.

Both precision and scale arguments are optional in the numeric Postgres data type.

• No arguments: If we do not specify any arguments, it can store any precision and scale data.
• Single argument: In case we specify a single argument, it is considered as the precision of the column with scale defaulted to 0. For example, you can define a 5-digit whole number using numeric(5).
• Precision with scale: In this case, we specify both precision and scale for the numeric Postgres data type. For example, numeric(5,2) defines a column with a precision of 5 and scale of 2.

Suppose we have the number 1234.56. In this number, we have precision 6 with a scale of 2. Here, we have a column [product_price] with numeric(5,2) data type.

Now, we’ll insert a few records in this table. In the output, it will convert the values.

In the output, it rounds the decimal component as per defined scale. For example, 100.246 converts to 100.25.

Similarly, if you insert a value that does not fit into the data type length, you will get numeric field overflow errors.

Note: Postgres data types are supported up to 131072 digits before, and 16383 after the decimal point.

Floating point

The floating point data type represents decimal numbers without exact and consistent precision.

Since the floating point data type is inexact, it might result in different values when storing and retrieving a value. Therefore, you should use the numeric data type for exact storage and calculations. If you compare two floating-point numbers, it might not work as expected.

Pgadmin is the most popular tool for database development of PostgreSQL. It provides a rich graphical user interface (GUI) for object deployments. You can download the latest version of pgAdmin here.

In the pgAdmin tool, you won’t find the floating point data type in its graphical interface. However, if you create a Postgres table with the script, it works. When you generate the script of the table, it maps internally to the double-precision data type.

Data types for text and characters

We can divide the data types for text and characters into two categories:

• Fixed length
• Variable length

Suppose users visit my blog and leave their comments, questions and feedback in a text box along with their name. In this case, we might have two columns: customer name and comments. For the first column, customer name, we might use a fixed length data type. For the comments column, we’d want more flexibility since users might want to use a single word, single line or multiple lines; therefore, we use a variable length data type.

• If we do not specify any number in char(n), it stores a single character.
• For an integer(positive) value, Postgres stores a fixed length character based on the specified number of characters.

For example, in the below table, we compare char(n) and varchar(n).

You cannot store a string more than the specified length, and in this case, it provides the error SQL state 22001.

Booleans

We use a Boolean data type for expressing true or false values. For example, in a product table, we can use this variable to check whether the product is available or not. It can store True, False or NULL values within it.

• True: Its equivalent values are true, t, yes, y, on and 1.
• False: Its equivalent values are false, f, no, off and 0.
• NULL: Unknown state.

As shown below, the Boolean data type converts the equivalent values into true or false.

Dates and time

Postgres supports data types for dates, times and intervals.

Dates format

As shown below, the default stored format is yyyy-mm-dd.

Time format

In the time data type, we can store a time without time zones.

Timestamps

A timestamp data type is a combination of date and time with the time zone.

Intervals

For example, in the below image, we calculate the timestamp 1 year 02 hours 30 minutes before the current timestamp.

Similarly, in the below screenshot, we calculate the timestamp of 10 days 5 hours 21 minutes 8 seconds from the current timestamp.

Network address type

Suppose your application stores IP addresses of users or sessions. For this purpose, you can use network address data types.

It supports the following data types.

Geometric data types

Geometric data types are used to represent two-dimensional spatial objects.

Binary data type

The binary data type is useful to store the binary strings that are a sequence of bytes.

JSON data types

Postgres supports JavaScript Object Notation (JSON) data with specific data types. It has both the JSON and JSONB data types.

JSON:

• It is an extension of the text data type, but it has a JSON validation feature.
• Data retrieval is relatively slow in this data type.
• Data insertion is fast for JSON data type.

JSONB:

• It represents JSON data in a binary format.
• Data retrieval is relatively fast in this data type.
• Data insertion is slow for JSON data type.

Best practices using Postgres data types

• You must use the smallest Postgres data types that have a maximum match for your column data. For example, use an integer data type for regular uses. If you require an extensive range of values, you can use bigint.
• Avoid using a text data type unless you have specific requirements.
• Carefully select the data type for each table column.
• Consider the data storage requirement for your data types. For example, if you choose char(n) data type, it uses the maximum storage irrespective of inputting a smaller string while varchar(n) stores only the actual string size.
• It is advised that you evaluate queries using execution plans and avoid implicit data conversions for an optimized performance.