What are the Data Types in PostgreSQL?

Introduction

When you create a table in a PostgreSQL database, you need to specify a data type for each column in that table. The data type is a critical piece of information– it defines what kind of data can be stored in a given column. For example, if you have a column with an integer data type, you can’t use it to store string values. In this article, we’ll take a closer look at several common data types in PostgreSQL and learn how they’re used.

Numeric types

In PostgreSQL, there are two main categories of numeric data types: integers and floating-point numbers.

Integers

• The small integer, or smallint, is used to store a limited range of whole numbers, from -32,768 to +32,767.
• The integer, or int, is used to store a wider range of whole numbers, from -2,147,483,648 to +2,147,483,647.
• The bigint data type can store the largest range of whole numbers, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Floating Point Numbers

• The numeric data type is able to store floating-point numbers that have up to 131,072 digits preceding the decimal point and up to 16,383 digits following the decimal point.
• The real data type can store floating-point numbers with a precision of six decimal points.
• The double data type can store floating-point numbers with a precision of 15 decimal points.

In addition to the integer and floating-point data types described above, another numeric type worth mentioning is the serial data type. When the serial type is assigned to a column, integer values are automatically generated and incremented for the column.

Boolean types

There are three possible values for a column with a boolean data type: true, false and null. When data is stored in a Boolean column, it gets converted into one of those three values:

• “1”, “yes”, “y”, “t” and “on” are converted to true
• “0”, “no”, “n”, “f” and “off” are converted to “false”

Character types

There are three character types in PostgreSQL:

• The char(n) data type has a fixed length of n characters; if the inserted string is shorter than length n, spaces will be used to pad the string to the specified length.
• The varchar(n) type is a variable-length character type with a maximum length of n characters.
• The text data type is another variable-length type, but it’s unlimited in length.

Temporal types

PostgreSQL offers several data types to help you store date and time data. The main types are:

• The date type is only used to store date values.
• The time type is used to store time-of-day values.
• The timestamp type can store both date and time values.
• The timestamptz data type is similar to the timestamp type, except that it includes time zone information.
• The interval data type stores values that represent periods of time.

Array

The array data type is a versatile type that can hold arrays of integers, arrays of strings and many other types of arrays. For example, this type can be used to hold the months or the year or the days of the week.

UUID

The UUID data type can be used to store Universal Unique Identifiers, which are 128-bit numbers generated by an algorithm. This type is useful for ensuring uniqueness within a database.

JSON

There are two data types in PostgreSQL for storing JSON data. These types accept nearly identical values, but there’s a difference between them in terms of efficiency:

• The json data type stores input text exactly as it’s inserted, which means that it needs to be re-parsed every time it’s processed.
• The jsonb data type stores input in a binary format. While the conversion to binary makes it a bit slower to insert, the data can be processed more quickly.

Conclusion

If you’re using a PostgreSQL database to store and manage data, it’s important to have a solid grasp on the data types that are available for use. Although users have the ability to create their own data types in PostgreSQL, that’s a topic for another article. PostgreSQL offers a robust set of native data types that should be more than enough to get you started. This overview of the various data types in PostgreSQL provides a handy reference that you can refer to when you create tables in your own database implementation.