Different Data Types in PostgreSQL
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 statuscommand to confirm the status is
activeand then press the CTRL + C keys to exit.
psql -Vcommand 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:
psql someUserName -h 127.0.0.1 -d some_database
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
FALSEor 1/0, algebraic logical expressions, but have a total of three states if
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
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
“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:
CREATE TABLE TABLE_NAME(column_name, TYPE_GOES_HERE, CONSTRAINTS [OPTIONAL]);
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:
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:
CREATE TABLE book(
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
TEXT value types. The following
CREATE TABLE example SQL statement includes three different character types:
CREATE TABLE fruits(
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
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 +/-
BIGSERIAL: This is a very large, eight-byte, auto-incrementing integer that goes from
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
NUMERIC: The numeric data type is a number that can be limited to a user-specified length, but may contain up to 131072 digits.
REALThis is a four-byte specified decimal value that can be up to six digits long.
SERIAL: An incremental integer that reaches from
SMALLINT: This is an integer having a value range limited to +/-
SMALLSERIAL: An integer that can range from
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
VARCHAR value types allows for passing an integer into parenthesis to specify the length of the character data type.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started