What are the Data Types in PostgreSQL?
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.
In PostgreSQL, there are two main categories of numeric data types: integers and floating-point numbers.
- 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.
bigintdata 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
numericdata 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.
realdata type can store floating-point numbers with a precision of six decimal points.
doubledata 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.
There are three possible values for a column with a
boolean data type:
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
- “0”, “no”, “n”, “f” and “off” are converted to “false”
There are three character types in PostgreSQL:
char(n)data type has a fixed length of
ncharacters; if the inserted string is shorter than length
n, spaces will be used to pad the string to the specified length.
varchar(n)type is a variable-length character type with a maximum length of
textdata type is another variable-length type, but it’s unlimited in length.
PostgreSQL offers several data types to help you store date and time data. The main types are:
datetype is only used to store date values.
timetype is used to store time-of-day values.
timestamptype can store both date and time values.
timestamptzdata type is similar to the
timestamptype, except that it includes time zone information.
intervaldata type stores values that represent periods of time.
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 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.
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:
jsondata type stores input text exactly as it’s inserted, which means that it needs to be re-parsed every time it’s processed.
jsonbdata 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.
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.