An Overview of PostgreSQL Data Types

It’s vital to know what you actually store in your database. That’s why every column has its data type. There are numerous PostgreSQL data types across various categories. Read on to see which data type suits which use case!

In this article, we’ll go through the most common data types used in PostgreSQL.

PostgreSQL, also called Postgres, is a well-known open-source object-relational database management system (DBMS). It is SQL-compliant, extensible, and includes all standard relational database features, such as database constraints, transactions, views, triggers, stored procedures, and more.

If you're looking for comprehensive SQL course in PostgreSQL, I recommend the interactive SQL from A to Z in PostgreSQL learning path. It contains over 800 exercises and covers the basic, intermediate, and advanced topics of SQL in PostgreSQL.

In this article, I’ll focus on one of Postgres’ basic (but crucial) features: data types. We’ll start with numerical data types and work our way through the text data types, date and time data types, BOOLEAN data types, and beyond. For each of the presented data types, we’ll examine its syntax, storage size, and use cases. Let’s get started!

Numerical Data Types in PostgreSQL

To store data like salaries, prices, quantities, or ID numbers, we use numerical data types. The world of numbers is very vast; thus, there is a need to store different numbers, like whole numbers or rational numbers, in different data types. Let’s see how the numbers are managed in PostgreSQL.

Integer Data Types: INTEGER vs. BIGINT

Both INTEGER and BIGINT data types are used to store whole numbers in PostgreSQL. The difference is the range of numbers allowed to be stored in each type:

Numerical Data Types in PostgreSQL

To store data like salaries, prices, quantities, or ID numbers, we use numerical data types. The world of numbers is very vast; thus, there is a need to store different numbers, like whole numbers or rational numbers, in different data types. Let’s see how the numbers are managed in PostgreSQL.

Integer Data Types: INTEGER vs. BIGINT

Both INTEGER and BIGINT data types are used to store whole numbers in PostgreSQL. The difference is the range of numbers allowed to be stored in each type:

The INTEGER data type is more commonly used, as it offers a better balance between range, storage size, and performance. BIGINT is usually chosen when INTEGER’s range is too small for the purpose. As you may expect, INTEGER takes less disk space than BIGINT.

The INTEGER data type can store a maximum of 10 digits; an error is thrown if more digits are present. On the other hand, BIGINT can store up to 19 digits before an error is thrown. That leads us to a simple conclusion: if we want to store extremely large numbers, we should use BIGINT rather than INTEGER.

Let’s look at a typical use case for the INTEGER data type. We’ll create a Stock table that stores products and the respective quantities that are available in our store:

It is quite unlikely that we’ll have more than 2 billion pieces of a certain product in our stock; hence, the best choice for the data type of the Quantity column is INTEGER. As mentioned before, it offers the best performance and does not take too much disk space. So, wherever possible, it’s advised to use this data type for numerical values.

Now, let’s see when we can make use of the BIGINT data type. Nowadays, the storage of log files might be a troublesome task; log file data grows every second. We use various techniques to efficiently search log files, and some of them might require the BIGINT data type. For example, storing an identifier for each log entry may result in a number that is a lot bigger than 2 billion. This means the value is outside of INTEGER’s range and we must use BIGINT to be able to store it.

Here’s another scenario: performing calculations on large integer numbers may easily end up with a number greater than 2,147,483,647 or with a number less than -2,147,483,648. To store the results of these calculations in a PostgreSQL database, we must use the BIGINT data type.

Decimal Data Types: DECIMAL vs. NUMERIC

The DECIMAL and NUMERIC data types are equivalent in PostgreSQL. Both of them have a variable storage size, i.e. the storage size depends on the number of digits contained.

As opposed to INTEGER and BIGINT data types that can store only whole numbers, the DECIMAL and NUMERIC data types can store rational numbers. They can store 13,1072 digits before the decimal point and up to 16,383 digits after the decimal point.

Due to the high amount of numbers that can be stored before and after the decimal point, DECIMAL and NUMERIC data types are the best choice for calculations that require exactness. You could easily store monetary amounts in DECIMAL or NUMERIC columns and be certain that not a single cent will be lost!

Before we talk about the syntax, let’s define a couple of terms. Precision is the total number of all the digits stored in a DECIMAL or NUMERIC value. Scale is the number of digits after the decimal point. The number 647.98574 has a precision of 8 and a scale of 5.

Considering these two arguments – precision and scale – there are 3 ways of defining DECIMAL and NUMERIC types:

Please note that when you  explicitly specify the precision, its maximum value is 1,000 (as opposed to the limits imposed when declaring DECIMAL or NUMERIC without any arguments).

Let’s look at some usage examples of these data types. We have an Orders table that stores orders made by a web shop’s customers:

We could not possibly use any of the integer data types for the TotalPrice column because the decimal part would be lost. That’s why we must decide on either the DECIMAL or NUMERIC data type for the TotalPrice column.

Another good example would be a table that stores currency exchange rates, which require even more digits after the decimal point – and what follows may require more storage space.

The DECIMAL and NUMERIC data types allow you to have up to 16,383 digits after the decimal point, so any calculations of physical dimensions (e.g. width, length, or height) or money could make use of these data types. Even any of the physical constants could be stored as DECIMAL or NUMERIC. Ok, maybe except pi (π), but which data type can store an infinite number of digits after the decimal point?!

To give you a better view of the storage size of these fixed-precision data types, the standard requirement is 2 bytes for each group of 4 decimal digits, plus an additional 3 to 8 bytes as overhead.

Now, let’s look at the floating-point data types.

Floating-Point Data Types: REAL vs. DOUBLE PRECISION

REAL and DOUBLE PRECISION data types can store rational numbers. However, compared to NUMERIC and DECIMAL data types, REAL and DOUBLE PRECISION can store even more digits.

The REAL data type has a storage size of 4 bytes and a minimum precision of 6 decimal digits. The DOUBLE PRECISION data type allows slightly bigger numbers; it has a storage size of 8 bytes and a minimum precision of 15 decimal digits.

It is important to note that the REAL and DOUBLE PRECISION data types are inexact. This means that when a particular number crosses the limit of allowed digits, it is then rounded up; this might cause some data discrepancies after retrieval.

These floating-point data types can also store 3 special values. These special values are 'Infinity', '-Infinity', 'NaN'. The first two are easy to understand, as they represent positive and negative infinity. The last one means ‘not a number’ and represents a piece of data that cannot be converted to or presented as a number. Some NaN examples are:

• Dividing a number by zero; as we know from our math classes, dividing a number by zero ends in an undefined number. This undefined number is represented as NaN in PostgreSQL,
• The square root of a negative number; the well-known imaginary number i stands for the square root of -1. Taking the square root of a negative number results in an imaginary number that is also represented as NaN in PostgreSQL.

To summarize, the REAL and DOUBLE PRECISION data types should be used whenever exact values are not necessary and the values are going to be rounded up anyway.

Why would you choose these data types? As opposed to NUMERIC and DECIMAL, REAL and DOUBLE PRECISION, although inexact, offer better performance and faster operation.

SQL’s numerical data types are such a vast subject that they require a separate article – and that’s what we did! In Understanding Numerical Data Types in SQL, you will find insightful explanations and extensive usage examples of numerical data types.

Text Data Types in PostgreSQL

Storing text data (e.g. names or descriptions) in a database is incredibly common. To do it, we use text data types that are widely available in all relational database engines. Let’s look at the specific data types in PostgreSQL.

VARCHAR

The VARCHAR(n) data type – or, to give it its full name, CHARACTER VARYING(n) – is used to store strings up to n characters in PostgreSQL; of course, n must be a positive integer.

If you try to store a string of more than n characters, an error is thrown (unless the extra characters are spaces, in which case they are truncated). You may choose to not specify n at all; then VARCHAR can store a string of any arbitrary length.

The storage size of VARCHAR(n) depends entirely on the length of the string that it stores. In the case of short strings, the overhead is 1 byte; for long strings, the overhead is 4 bytes. The longest string that can be stored can take up to 1 GB of storage.

The VARCHAR data type can be implemented in two ways:

1. VARCHAR(20) that limits the characters stored to 20, or
2. VARCHAR that does not imply any limits on the number of characters stored.

This data type is most commonly used to store names, textual locations, or descriptions.

CHAR

The CHAR(n) data type – full name: CHARACTER(n) – stores strings up to n characters, where n must be a positive integer.

A string value that contains more than n characters cannot be stored in CHAR(n) and will result in an error. A string that contains less than n characters is followed by whitespaces to fill up the ‘missing’ characters up to n characters. However, these whitespaces are neglected when comparing CHAR-type values.

You can also use CHAR without specifying n. In such a case, the default string length is one (CHAR(1)).

The differences between CHAR and VARCHAR are:

• VARCHAR includes whitespace characters during comparisons; CHAR does not.
• CHAR uses trailing whitespaces if the string size is less than n; VARCHAR does not.

CHAR and VARCHAR have the same storage requirements, as described in the section on VARCHAR above.

TEXT

In Postgres, the TEXT data type is equivalent to using VARCHAR without its argument.

The TEXT data type was created for convenience. It obeys exactly the same rules as the VARCHAR data type and can be used for the same purposes.

In PostgreSQL, there is no difference in performance when using any of the above text data types.

Date and Time Data Types in PostgreSQL

Every day, someone (or many someones!) ask What time is it? or What’s today’s date?. As important as time and date are in our daily life, we could not leave them without their own SQL data types. PostgreSQL provides a number of data types to store dates, times, or both. Let’s go through the most universal ones.

DATE

The DATE data type is used to store a date that consists of day, month, and year. It doesn’t store time, so its resolution is 1 day. Its storage size is 4 bytes.

It’s highly recommended that you store dates in the DATE type and not in any of the text data types. By default, PostgreSQL uses the yyyy-mm-dd format to store and insert date values. You can specify your own format when inserting date values to avoid any confusion.

Let’s create a Persons table that stores people’s names and birthdays.

CREATE TABLE Persons (
	Id integer PRIMARY KEY,
	FirstName VARCHAR(30),
	LastName VARCHAR(50),
	Birthday DATE
);

Now, let’s insert some records into the Persons table.

INSERT INTO Persons VALUES (1, 'David', 'Johnson', '1990-08-12');
INSERT INTO Persons VALUES (2, 'Meghan', 'Black', '1995-10-03');
INSERT INTO Persons VALUES (3, 'Taylor', 'Anderson', '2000-01-01');

When inserting date values, we use the default PostgreSQL format, i.e. yyyy-mm-dd. But we could select the date values in our preferred format, as shown below:

SELECT Id, FirstName, LastName, TO_CHAR(Birthday, 'mm/dd/yyyy')
FROM Persons;

And this returns…

Now that we know how to store dates, let’s move on to the data types that allow us to store time.

TIME

The TIME data type stores only time; by default, it doesn’t include time zone information. Its storage size is 8 bytes (or 12 bytes with time zone). The resolution of time stored in this data type is up to 1 microsecond, i.e. you are allowed to have up to 6 fractional digits in the seconds field.

The syntax of using the TIME data type is as follows.

TIME [ (p) ] [ WITHOUT TIME ZONE ]

or

TIME [ (p) ] WITH TIME ZONE

The p argument is optional and specifies the number of fractional digits in the seconds field. The second argument is not necessary if we do not use the time zone. To use time zones, we should use the WITH TIME ZONE addition.

In general, you can specify the date along with time; however, this date will be ignored. The one exception is when you use a time zone that has its own daylight-savings rule; then the date is required to determine the time correctly. PostgreSQL does not recommend using TIME WITH TIME ZONE; instead, it recommends TIMESTAMP WITH TIME ZONE, which will be discussed in the next section.

Let’s look at an example. We’ll create an Events table and store the starting and ending times of each event.

CREATE TABLE Events (
	EventId integer,
	EventName VARCHAR(50),
	EventStart time,
	EventEnd time
);

Let’s insert some values into the Events table.

INSERT INTO Events VALUES (1, 'Introduction', '08:00:00', '10:15:00');
INSERT INTO Events VALUES (2, 'Insights', '11:00:00', '12:30:00');
INSERT INTO Events VALUES (3, 'Presentation', '14:00:00', '16:20:00');

The default PostgreSQL time format is hh:mm:ss. After selecting from the Events table, we get:

Now, let’s move to the TIMESTAMP data type that is recommended for use when including time zones.

TIMESTAMP

Until now, we’ve looked at the DATE and TIME data types, which can only store dates and times, respectively. Very often we need to store both date and time together; the TIMESTAMP data type makes it possible in PostgreSQL.

There are two variations of TIMESTAMP data type: one is without time zone information and the other is with time zone information. Let’s look at them individually.

TIMESTAMP WITHOUT TIME ZONE

This stores date and time data without a time zone. Its storage size is 8 bytes and has a 1 microsecond resolution (like the TIME data type).

The syntax is:

TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ]

However, as in the case of the TIME data type, the WITHOUT TIME ZONE argument is not necessary; by default, TIMESTAMP does not include time zone information. The p argument defines the number of fractional digits in the seconds field.

When using TIMESTAMP WITHOUT TIME ZONE, providing a value for the time zone is not going to raise an error – it will simply be ignored.

Let’s look at the Events table again. This time, we’ll define the start and end of an event as a TIMESTAMP.

CREATE TABLE Events (
	EventId integer,
	EventName VARCHAR(50),
	EventStart TIMESTAMP,
	EventEnd TIMESTAMP
);

Let’s insert some values into the Events table.

INSERT INTO Events VALUES (1, 'Introduction', '2021-05-24 08:00:00', '2021-05-24 10:15:00');
INSERT INTO Events VALUES (2, 'Insights', '2021-05-24 11:00:00', '2021-05-24 12:30:00');
INSERT INTO Events VALUES (3, 'Presentation', '2021-05-24 14:00:00', '2021-05-24 16:20:00');

As you can see, we define the date and time separated by a space.

And this is what we get after selecting all rows from the Events table:

In the output, the date and time are separated with the letter T. The letter Z at the end stands for zero hour offset (which signifies the UTC time zone), although time zones are not considered here.

Speaking of time zones, let’s go to the next variation of the TIMESTAMP data type.

TIMESTAMP WITH TIME ZONE

The rules are the same as for TIMESTAMP WITHOUT TIME ZONE. The storage size is also 8 bytes and the resolution is 1 microsecond.

The difference is that now we can include information about the time zone. Other than the argument, the syntax is also the same:

TIMESTAMP [ (p) ] WITH TIME ZONE 

Nowadays, many events take place online so that participants from all over the world can attend. That’s why it is essential to include information about the time zone. Let’s modify our Events table again…

CREATE TABLE Events (
	EventId integer,
	EventName VARCHAR(50),
	EventStart TIMESTAMP with time zone,
	EventEnd TIMESTAMP with time zone
);

…and insert some values:

INSERT INTO Events VALUES (1, 'Introduction', '2021-05-24 08:00:00 +01:00', '2021-05-24 10:15:00 +01:00');
INSERT INTO Events VALUES (2, 'Insights', '2021-05-24 11:00:00 +01:00', '2021-05-24 12:30:00 +01:00');
INSERT INTO Events VALUES (3, 'Presentation', '2021-05-24 14:00:00 +01:00', '2021-05-24 16:20:00 +01:00');

If you select all the information from the Events table, you’ll see that time zone information is not explicitly visible. Instead, PostgreSQL picks up your system’s time zone and converts the date and time to the equivalents in your time zone.

Assuming that we are in time zone -04:00, we will get the following output.

Time zone formats can be specified using either the form presented above or the time zone name (e.g. America/New_York or EDT).

Now we know the types most frequently used to store dates and times. The next data type is used to manipulate date and time values. Let’s see how it works!

INTERVAL

The INTERVAL data type in PostgreSQL is used to store the amount of time that can be added or subtracted from the data types defined above.

It has a storage size of 16 bytes and a resolution of 1 microsecond (like any data type that can store time).

Its syntax is ...

INTERVAL [ fields ] [ (p) ] 

 … where p is the number of fractional digits in the seconds field (0 to 6) and the fields argument is a placeholder for the units that an INTERVAL can store. Please note that whenever arguments are enclosed in square brackets, they are optional.

Let’s see some examples.

• INTERVAL YEAR (3) can store only values with units of years, such as 1 year or 4 years. Its resolution is 1 millisecond.
• INTERVAL DAY TO SECOND (6) can store days, hours, minutes, and seconds (e.g. 3 days, 2 hours, 5 minutes, and 10 seconds). Its resolution is 1 microsecond.

Coming back to our Events table, we can select all its data along with the duration of each event. We’ll get the events’ duration by subtracting their start timestamp from their end timestamp, as follows.

SELECT EventId, EventName, EventStart, EventEnd, 
       EventEnd-EventStart as Duration
FROM Events;

And we get the following output:

We could also modify the start and end times of the events on the fly by running the following SELECT statement.

SELECT EventId, EventName, 
EventStart + INTERVAL '1 HOUR', 
EventEnd + INTERVAL '1 HOUR' 
FROM Events;

And here we get –

Now you know the date and time storage options in a PostgreSQL database. Let’s move to the next data type.

The BOOLEAN Data Type in PostgreSQL

The BOOLEAN data type is one of the few whose name does not reflect what it is used for. Maybe it will help you remember its usage if I tell you a short story.

The BOOLEAN data type is named after George Boole, a famous 19^th century English mathematician, philosopher, and logician. He came up with Boolean Logic, or the idea that any value could be either true or false (and nothing in between). His idea would lay the foundation for today’s information technology.

Now that you know the background, it makes sense that the BOOLEAN data type represents either 1 (i.e. true) or 0 ( false). As you may expect, the storage size of a BOOLEAN is 1 byte (8 bits).

Let’s look at one of the many usages of the BOOLEAN data type. We’ll start with creating the Tasks table.

CREATE TABLE Tasks (
	TaskId text,
	Completed boolean
);

The Tasks table contains the TaskId and Completed columns. The TaskId column stores a unique identifier for each task; we’ll say its rows are linked to another table that stores each task’s details. The Completed column indicates the status of the tasks, which can be either completed (true) or not (false).

When inserting  values, we use true/yes/on/1 for the positive BOOLEAN value and false/no/off/0 for the negative BOOLEAN value:

INSERT INTO Tasks VALUES ('1F67H', true);
INSERT INTO Tasks VALUES ('2K57Y', off);

or

INSERT INTO Tasks VALUES ('1F67H', yes);
INSERT INTO Tasks VALUES ('2K57Y', 0);

That’s how easy it is!

After querying the Tasks table, don’t be surprised to get t or f values in the BOOLEAN column:

If you want to learn more about any of the above data types, check out our article on Data Types in SQL. It describes SQL data types without specifying any particular database engine features.

Even More PostgreSQL Data Types

You might think that the data types described in this article are more than enough to cover the wide range of data that can be stored in the database. But there are a lot more!

There are many specialized data types in PostgreSQL. Let’s briefly look at each category of supported data types:

• Monetary Types

Monetary data types are specialized to store currency amounts with fixed fractional precision. The name of this data type in Postgres is MONEY. Very straightforward!

• Binary Data Types

Binary data types store binary strings. These are not the same as common character strings; a binary string is a sequence of bytes that can store data such as pictures or videos.

• Enumerated Types

Enumerated types store a predefined set of values. In your daily life, you may encounter dropdown menus in different documents or forms. These are nothing but predefined values that might be stored as an enumerated data type.

• Geometric Types

Geometric types are interesting. They allow you to store two-dimensional geometric figures in your database using points on the coordinate system. A rectangle would be stored using its two diagonal points ((x1,y1),(x2,y2)).

Geometric data types are commonly used with geographical data. Check out our interactive course on PostGIS to learn more.

• Network Address Types

According to Google, there’s a question more common than What time is it? That question is What is my IP? Network address types store this information (i.e.  IPv4, IPv6, and MAC addresses) with precision. And it is advisable to use them for their purpose, as they will not allow you to insert an IP address in an incorrect way!

• Bit String Types

Bit string types store strings consisting of 1’s and 0’s. These types can be used to store bit masks of predefined or varying lengths.

• Text Search Types

Text search types support full-text search through a collection of documents to find the best match for the query.

• UUID Type

The UUID type stores Universally Unique IDentifiers. An algorithm-generated identifier is (almost) guaranteed to be unique in the whole known universe, but only if we consider all the identifiers generated by this specific algorithm.

• XML Type

The XML type stores XML data. The advantage of this type over text data types is that it checks the XML format and provides functions that operate on XML data.

• JSON Types

JSON types store JSON data. Like the XML type, they ensure the correctness of the JSON data and provide functions to operate on it.

• Arrays

If you are a programmer, you know about arrays; they provide a means to store multidimensional arrays of any data type in a single column. If you need to store multiple values in one column and still comply with the first database normalization rule – which states that column data should be atomic, or unable to be divided into smaller parts – you should use this data type.

• Composite Types

Composite data types could be compared to classes in an object-oriented programming language that consist of fields only. We must define them ourselves and we have the freedom to input any field within the composite type. It is easy to create a composite data type by using the CREATE TYPE complex_item_type AS (Field1 as DATA TYPE, Field2 as DATA TYPE) syntax and defining the field names and their types. Composite types can be nested within other composite types.

• Range Types

As the name indicates, range types store a range of values. These values could be an INTEGER, TIMESTAMP, or any other data type. In our Events table, for example, we could define a Participants column that stores the minimum and the maximum number of participants that can attend the event. The Participants column could easily use one of the range types.

• Domain Types

Domain types are another category of data types that can be customized according to your requirements. You can define a domain using an existing data type and then optionally restrict its values. For example: CREATE DOMAIN age AS integer CHECK (VALUE >= 21). Here, age becomes a new data type that works exactly as an INTEGER, but it only accepts values that are greater than or equal to 21.

• Object Identifier Types

Object identifier types are internal PostgreSQL types that uniquely identify each of the system tables.

• PG_LSN (Log Sequence Number) Type

As the database is used, log files increase every day and searching through them becomes a time-consuming task. Thus, we have the PG_LSN data type. It is used to store log sequence numbers, or LSNs. Each LSN is a pointer to a specific location in the Write-Ahead Log so that you can quickly find what you are looking for.

• Pseudo-Types

Last but not least, we have pseudo-types. Although they cannot be used as column data types, these types are crucial for defining non-traditional function arguments and return values. For example, you could define the function argument to be of the ANYELEMENT type, which means that the function can take any data type as its argument.

Now, with your knowledge of the data types available in SQL, you can proceed to our track on Creating Database Structure, where you will learn how to properly use SQL data types to create reliable database designs. To find out what this track is all about, check out this article.

Put Your Knowledge of Postgres Data Types to Work!

There it is! All of the data types described (or just mentioned) here make up the data types available in PostgreSQL at the time of this writing.

The most common data type categories – e.g. numerical, text, date and time, and BOOLEAN – are probably well known to you from work or your studies. But it is a great advantage to also be aware of the less common ones so that you know what is at your disposal in those situations which require special treatment.

Now you are ready to practice what you learned throughout this article. That’s why we prepared a course on Data Types in SQL so you can really firm up your knowledge of available data types.

Good luck!