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 typesThis 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

A 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

CIDR

7 or 19 bytes

IPV4 and IPv6 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.

 

Anonymous
Related Content