Arindam Mondal

An introduction to PostgreSQL Data Types with examples

September 15, 2023 by

This article will show PostgreSQL Data Types with various examples.

Data Types are an important part of a database. It represents values associated with it. Choosing the right data type for a table is one of the most important tasks because it determines the kind of data we want to store in a table. While creating a table you must specify a data type for each column. A column can store a specific type of data, like integer, string, Boolean, floating points, and so on. In this article, we are going to discuss PostgreSQL data types.

Introduction

PostgreSQL is one of the most popular and widely used open-source relational database systems. The most popular PostgreSQL data types are numeric data type, character data type, date/time data type, Boolean data type, Binary data type, Monetary type, Enumerated type, Geometric type, Network type, and UUID type. Let’s discuss this data type below:

Numeric Types

The numeric data type is used to store numeric data type in a table. PostgreSQL supports various numeric data types including smallint, integer, bigint, decimal, numeric, real, double precision, serial, bigserial.

It has below data types:

Integer types – Whole number data types are smallint, integer and bigint. We can store whole numbers in these data types and fractional parts are not supported.

  • smallint – The smallint datatype has the lowest range (-32768 to +32767) among the whole number data types. So, it may be used for only specific requirements. It requires 2 bytes of storage size. For example, it can store the age of a person.
  • Integer – The most common whole number data type is the integer as it has a range -2147483648 to +2147483647 which is the almost perfect choice for most real-world applications. The integer is probably the most used PostgreSQL data type. It requires 4 bytes of storage size. In case you need a bigger data type than an integer then a bigint data type is available.
  • Bigint – If you need to store a very big number then the bigint data type is the right option as it has a range -9223372036854775808 to 9223372036854775807. The data type can store big numbers as much as 9 quintillion (If you are not sure about how big it is, the list goes million, billion, trillion, then quadrillion). It requires 8 bytes of storage size. I practically have not seen much use of bigint data type.

Let us see an example of the integer data type, in the below example we have created three columns with id smallint, id2 integer and id3 bigint data type and inserted 1,10,100 to the id, id2, and id3 columns respectively:

create table integer

Output:

integer output

  • Arbitrary Precision Numbers
    • Numeric – The Numeric datatype can store very large numbers. It is best used for storing monetary amounts and quantities where exactness is required.
  • Floating Point types -The data types real and double are variable precision numeric types available in PostgreSQL data type.
    • Real – It has 4 bytes storage size and supports 6 decimal digit precision.
    • Double – It has 8 bytes storage size and supports 15 decimal digit precision.
  • Serial types – The data type serial and bigserial are used to provide unique values for a column, it is an auto-increment integer.
    • Serial – It has 4 bytes storage size; the range is 1 to 2147483647
    • Bigserial – It has 8 bytes of storage; the range is 1 to 9223372036854775807.

Character Data Type

PostgreSQL data types provide various character data types to store character data as per the requirement. character (n), char(n), character varying (n), varchar (n), text.

  • character varying (n), varchar (n) – Here n is the number of characters to store. Both data types store variable length strings.
  • character (n), char(n) – Here n is the number of characters to store. Both data types store fixed-length strings.
  • text – The text data type stores strings of any length.

There is no significant performance difference in these three data types. In most real-time applications text or character is varyingly used.

Date/Time Types

PostgreSQL data types provide below date and time data type:

  • timestamp [ (p)] [ without time zone] – It stores both date and time without time zone, it has 8 bytes storage size.
  • timestamp [ (p)] with time zone – It stores both date and time with time zone, it has 8 bytes storage size.
  • Date – It stores only date and no support for time, it has 4 bytes storage size.
  • time [ (p)] [ without time zone] – It stores only time without time zone and no support for a date, it has 8 bytes storage size.
  • time [ (p)] with time zone – It stores only time with time zone and no support for a date, it has 12 bytes storage size.
  • The interval [ fields] [ (p)] – It stores time intervals and it requires 12 bytes of storage

In the below example, we are creating a table with columns coldate as date and coltime as time:

data time table

Output:

date time table output

Date Output

We can also format date output as required, To_CHAR() function is used to format the date. The TO_CHAR() function uses two parameters. The first parameter is the input date that you want to format and the second one is the format specifier.

In the below example we used the format ‘Mon dd, yyyy’:

date output convert

date output

Boolean data type

PostgreSQL data types provide Boolean data types. The Boolean data type can have various states:” true”,” false”, and a third state,” unknown”, which is similar to SQL null value.

Binary Data Types

The binary data type in PostgreSQL offers storage of binary strings.

Monetary Types

PostgreSQL data types provide a money data type for currency amounts. It has 8 bytes storage size.

Enumerated Types

We can store ordered sets of values in enumerated or enum data types. It is like enum types which are available with other programming languages. For example, we can store the months of the year:

Special data type

In the above section we discussed the primitive data types, PostgreSQL also offers special data types for geometric and network-related activity.

Geometric Types

PostgreSQL data types offer geometric data types to represent spatial objects. These geometric data types are point, line, lseg, box, path, polygon, and circle.

  • point – It is a point on a plane. The syntax is (x, y).
  • Line – Lines are represented by two different points. The syntax is ((x1, y1),(x2,y2))
  • Lseg – It shows a finite line segment. The syntax is ((x1,y1),(x2,y2)).
  • Box – It is available by the pair of points from the opposite corner of the boxes. The syntax is ((x1, y1), (x2, y2)).
  • Path – A path can be open or closed. It is represented by lists of the connected points. The syntax of the path is ((x1, y1),…)
  • Polygon – It is like the closed paths, the main difference is that a polygon essentially includes the area within it, while a path is not. The syntax of the path is ((x1, y1),…)
  • Circle – A circle has a center point and radius. The syntax is < (x, y), r> (center point and radius)

Network Address Type

PostgreSQL data types provide data types to store IP Addresses (IPv4 and IPv6) and Mac addresses. Let us discuss this data type below:

  • Inet – It is used IPv4 and IPv6 hosts and networks. inet requires a 7- or 19-byte storage size.
  • Cidr – It is used IPv4 and IPv6 networks. inet requires a 7 or 19-byte storage size.
  • Macadr – We can store the MAC address in the macaddr data type. It uses 6 bytes of storage space.

UUID Type

PostgreSQL data types offer the UUID (Universally Unique identifiers) which is a 128-bit unique identifier that is generated by an algorithm. A UUID is a lower-case hexadecimal digit, it is a combination of several groups separated by hyphens, the first portion has 8 digits followed by three groups of 8 digits followed by a group of 12 digits, a total of 32 digits measures 128 bits. An example of UUID is given below:

a0cdba99-9f0b-4ed8-aa6d-6cc9ab380b12

Conclusion

In this article, we discussed the most popular PostgreSQL data types.

Arindam Mondal
Latest posts by Arindam Mondal (see all)

Related posts:

  1. Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
  2. Exporting SSRS reports to multiple worksheets in Excel
  3. Getting started with Azure SQL Database using Azure CLI
  4. Identify unused tables of SQL databases
  5. Executing stored procedures from data pipelines in Azure Data Factory
Data types

About Arindam Mondal

Arindam is an experienced and highly motivated IT enthusiast who likes to leverage his technical expertise to address critical business needs. A self-guided learner who loves to learn every single day. He loves to indulge in cultural diversity and travel to newer destinations. Arindam has rich experience in Azure solution implementation and support activities for large enterprise clients, having worked on multiple developments and enhancement projects. He is currently based in India and is working as a Technical Lead for a leading MNC Company. A few significant facts about his career: * Has over 10.7 years of IT experience in Microsoft platforms. * Has around 8 years of experience in SQL database development (T-SQL/Performance tuning/CDC) and ETL-SSIS. * Has 5 years' experience in Azure Environment (Azure Data Factory, Azure Data Lake Storage, Azure SQL Database, Azure Cosmos DB, Azure Synapse Analytics)

168 Views