Different Data Types in PostgreSQL

Introduction

This tutorial will explain the different data types in PostgreSQL and will provide both an overview and detailed explanations for the Boolean, character and numeric data types. While some of the data types perform similarly, each has specialized functions that make it suited for specific operations in PostgreSQL.

Prerequisites for using Different Data Types in PostgreSQL

• PostgreSQL must be properly installed and running. Execute the service postgresql status command to confirm the status is active and then press the CTRL + C keys to exit.

• Execute the psql -V command to confirm the interactive PSQL command-line for PostgreSQL is installed and working properly. The results should resemble the following:

How to access PostgreSQL using the ‘psql’ command-line interface

To access a PostgreSQL database, execute the following psql command-line interface in a terminal window on the localhost server:

Next, enter the user password when prompted and then press “Return” to access to the database.

An Overview of the Different Data Types in PostgreSQL

Following is an overview of the different data types in PostgreSQL:

• BOOLEAN: Boolean data types are binary, such as TRUE/FALSE or 1/0, algebraic logical expressions, but have a total of three states if NULL is included.

• CHARACTER: This data type receives inputs and returns the outputs as character or number values that include strings and text blocks. The three different values types in PostgreSQL are VARCHAR, TEXT and CHAR.

• NUMERIC: This data types is commonly used to store numbers, varying in length of float and integer values, in database columns.

• TEMPORAL: The temporal data type is used for dates, timestamps and refers to any kind of temporal, or time-based, value such as hours, minutes, days, months, days of the week, etc.

• ARRAY. This is a builtin data type of an organized set of elements. It stores different kinds of data types inside a list-like variable or object.

• JSON: A text or string-type format that makes up documents, referred to as objects, of stored data that is nested inside of key-value pairs. This also includes the JSONB, or JSON binary, data type.

• UUID: The Universal Unique Identifier, referred to as UUID, data type is a text-type piece of data composed of randomly generated alpha-numeric characters with hyphens and 128-bit algorithmic identifiers of varying length, such as ec32a30d-e927-4c79-9367-5226096c4540.

• “Special” types: Special data types include those like enumerated, or ENUM, and geometric data types.

How to Specify a PostgreSQL Column’s Data Type

While connected to a PostgreSQL database, the column’s data types can be specified in a PostgreSQL table when the CREATE TABLE SQL statement is executed.

Be certain to specify the table name first, then followed by the column name, type and any optional constraint placed within parenthesis.

Following is an example template of the CREATE TABLE statement in PostgreSQL:

NOTE: A table must be created in pgAdmin or in the psql command-line interface.

The Boolean Data Type in PostgreSQL

A Boolean data type can have three states or values: TRUE, FALSE or NULL. To avoid having a Boolean column with only the two states of true or false, make sure to specify the NOT NULL constraint when creating the PostgreSQL table. To use Boolean, just type the keyword BOOL for the column’s data type.

How to creating a PostgreSQL table with a Boolean (BOOL) column

The following example utilizes a book table that uses a completed Boolean column to indicate whether or not a book record in the table has been completed:

NOTE: The BOOL value now represents whether it is true, false or null.

The Character Data Types in PostgreSQL

A character data type is composed of the CHAR, VARCHAR and TEXT value types. The following CREATE TABLE example SQL statement includes three different character types:

NOTE: The CHAR and VARCHAR value types allows for passing an integer into parenthesis to specify the length of the character data type being input.

The CHAR vs VARCHAR vs TEXT data types in PostgreSQL

CHAR, VARCHAR and TEXT all perform similarly. However, each has a specific use.

CHAR is for data made up of fixed-length data strings, such as a category of data that will always have the same number of characters. A good example of this is the abbreviations for the names for the U.S. states.

VARCHAR is for variable-length strings, or characters, when the text length for each table row in a particular column cannot be predicted.

TEXT datatype is for large blocks of text when is virtually impossible to predict the length of each column row, as it does not require that a length be specified.

Numeric Data Types in PostgreSQL

As the name implies, numeric data types are any numerical data types that are composed of floats, decimals, currency and integers. The different numeric types are as follows:

• BIGINT: A large-range integer that can go up to +/- 9223372036854775807.

• BIGSERIAL: This is a very large, eight-byte, auto-incrementing integer that goes from 1 to 9223372036854775807.

• DECIMAL: This numeric data type has a specific user-specified number that can have decimal places spanning thousands of digits. The decimal data type is ideal for values like Pi that have decimal places to infinity.

• DOUBLE PRECISION: This data type is a precise eight-byte variable that can have up to 15 decimal places.

• INTEGER: Integer is a four-byte number ranging from -2147483648 to 2147483647.

• NUMERIC: The numeric data type is a number that can be limited to a user-specified length, but may contain up to 131072 digits.

• REAL This is a four-byte specified decimal value that can be up to six digits long.

• SERIAL: An incremental integer that reaches from 1 to 2147483647.

• SMALLINT: This is an integer having a value range limited to +/- 32767

• SMALLSERIAL: An integer that can range from 1 to 32767.

Conclusion

This tutorial covered the different data types in PostgreSQL and provided details about the Boolean, character, and numeric data types. The article offered an overview of the different data types in PostgreSQL and covered how to create a PostgreSQL table with a Boolean column and specify a PostgreSQL column’s data type. The tutorial also gave a detailed breakdown of the CHAR, VARCHAR, TEXT and numerical data types used in PostgreSQL. Remember that a table must be created in pgAdmin or in the psql command-line interface and that the CHAR and VARCHAR value types allows for passing an integer into parenthesis to specify the length of the character data type.