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 isactive
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:
1 | 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 asTRUE
/FALSE
or 1/0, algebraic logical expressions, but have a total of three states ifNULL
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 areVARCHAR
,TEXT
andCHAR
.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 theJSONB
, 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 asec32a30d-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:
1 | 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: 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:
1 2 3 4 5 | CREATE TABLE book( book_id UUID, book_name TEXT, completed BOOL ); |
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:
1 2 3 4 5 6 | CREATE TABLE fruits( fruit_name VARCHAR(32), fruit_type CHAR(5), fruit_description TEXT, amount INT ); |
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 from1
to9223372036854775807
.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
to2147483647
.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 from1
to2147483647
.SMALLINT
: This is an integer having a value range limited to +/-32767
SMALLSERIAL
: An integer that can range from1
to32767
.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started