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.

Integer data type Length Lowest possible values Highest possible values
smallint 2 bytes -32768 32767
integer 4 bytes -2147483648 2147483647
bigint 8 bytes -9223372036854775808 9223372036854775807

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.

Integer data type Length Lowest possible values Highest possible values
smallserial 2 bytes 1 32767
serial 4 bytes 1 2147483647
bigserial 8 bytes 1 9223372036854775807

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.

Pseudo data types

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

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.

Generating table scripts

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.

Numeric Postgres data type

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

Converted values example

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.

Postgres data type length showing 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.

Integer data type Length Decimal digits precision
real 4 bytes 6
double precision 8 bytes 15

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.

Double precision Postgres 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.

Data type Synonym Description
Char(n) Character(n) Fixed length with blank space padding
Varchar(n) Character varying(n) Variable-length
text Variable unlimited length Variable unlimited length
  • 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).

Data type Length Input string Number of stored characters Stored string
Char 5 SQL 5 ‘SQL ‘
Varchar 5 SQL 3 ‘SQL’

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

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.

Boolean data type in Postgres conversion

Dates and time

Postgres supports data types for dates, times and intervals.

Dates format

Data type Storage Low value High Value Format
Date 4 bytes 4713 BC 5874897 AD yyyy-mm-dd

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

Stored default date format in Postgres

Time format

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

Data type Storage Low value High value Format
Time 8 bytes 00:00:00 24:00:00 ·HH:MM
·HH:MM:SS
·HHMMSS

The Postgres time data type

Timestamps

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

Data type Storage Low value High value Format
Timestamp 8 bytes 4713 BC 294276 AD yyyy-mm-dd hh:mm:ss

Timestamp data type in Postgres

Intervals

Data type Storage Low value High value
Interval 16 bytes -178000000 years 178000000 years

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

Timestamp example in Postgres

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

Second timestamp example in Postgres

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.

Data type Storage Description
INET 7 or 19 bytes IPV4 and IPv5 host and networks

Network IP address type in Postgres.

Data type Storage Description
INET 7 or 19 bytes IPV4 and IPv5 host and networks

Create Table in Postgres

Data types Storage Description
Macaddr 6 bytes Mac address
macaddr8 8 bytes MAC addresses (EUI-64 format)

Geometric data types

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

Postgres data types Storage Description
Point 16 bytes Point on a plane
line 32 bytes Infinite line
Lseg 32 bytes Finite line segment
box 32 bytes Rectangular box
Path 16+16n bytes Closed path (similar to polygon)
polygon 40+16n bytes Polygon (similar to a closed path)
circle 24 bytes Circle

Geometric data types in Postgres

Binary data type

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

Data type Storage Description
Bytea 1 to 4 bytes Variable-length binary string

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.

JSON data types n Postgres.

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.

JSONB data in binary format

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.

The zombie survival guide to database performance tuning

Don’t be a database zombie. Learn 6 expert steps for approaching modern database performance tuning.

Get the Guide

About the Author

Rajendra Gupta

Rajendra Gupta is a MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" in 2020 and 2021 at SQLShack.

Related Articles