Different PostgreSQL Data Types

Different PostgreSQL Data Types

PostgreSQL is an open-source relational database management system known for its robustness and extensibility. This also means PostgreSQL offers a variety of data types for users.

In this post, we'll look at the various data types supported by PostgreSQL. PostgreSQL supports the following data types: 

• Boolean
• Character Types [ such as char, varchar, and text]
• Numeric Types [ such as integer and floating-point numbers]
• Temporal Types [ such as date, time, timestamp, and interval]
• UUID [ for storing UUID (Universally Unique Identifiers) ]
• Array [ for storing array strings, numbers, etc.]
• JSON [ stores JSON data]
• hstore [ stores key-value pair]
• Special Types [ such as network address and geometric data]
• Monetary The monetary data type stores a numeric amount of money with fixed fractional precision
• Binary 
• Bit String The Bit string type stores strings of 1's and 0's
• XML
• Composite
• Object Identifier
• Pseudo Types

Now let’s get a quick review on how these different data types work and differ among others.

Boolean

The “bool” or ”boolean” keyword on PostgreSQL is necessary to initialize a Boolean data type. They can contain true, false, and null  values. Boolean data types are stored in the database with these conditions: 

• 1, yes, y, t, true values are converted to true
• 0, no, false, f values are converted to false

The boolean data are transformed and returned as follows when they are queried: 

• t to true
• f to false
• space to null

Characters

PostgreSQL has three character data types namely, CHAR(n), VARCHAR(n), and TEXT. 

• CHAR(n) is used for data(string) with a fixed length of characters with padded spaces.  In case the length of the string is smaller than the value of “n”, then the rest of the remaining spaces are automatically padded. Similarly for a string with a length greater than the value of “n”,  PostgreSQL throws an error.
• VARCHAR(n) is the variable-length character string. Similar to CHAR(n), it can store “n” length data. But unlike CHAR(n) no padding is done in case the data length is smaller than the value of “n”.
• TEXT is the variable-length character string. It can store data with unlimited length.

Numeric

In PostgreSQL, there are two different kinds of numbers: integers and floating-point numbers.

1. Integer: 

• Small integer (SMALLINT) has a range -32, 768 to 32, 767 and has a size of 2-byte.
• Integer (INT) has a range -2, 147, 483, 648 to 2, 147, 483, 647 and has a size of 4-byte.
• Serial (SERIAL) works similar to the integers except these are automatically generated in the columns by PostgreSQL.

2. Floating-point number: 

• float(n) is used for floating-point numbers with n precision and can have a maximum of 8-bytes.
• float8 or real is used to represent 4-byte floating-point numbers.
• A real number N(d,p) with d number of digits and p number of decimal points after, are part of numeric or numeric(d, p). These are generally very precise.

Temporal data type:

Date-and-time information is stored using this data type. There are 5 temporal data types in PostgreSQL:

• DATE is used to store the dates only.
• TIME is used to store the time of day values.
• TIMESTAMP is used to store both date and time values.
• TIMESTAMPTZ is used to store a timezone-aware timestamp data type.
• INTERVAL is used to store periods of time.

Arrays

In PostgreSQL, an array column can be used to hold an array of letters, an array of characters, or other kinds of data. It could be handy for preserving data, such as days of months, a year, or only a week, etc.

JSON

JSON and JSONB (Binary JSON), are two forms of JSON supported by PostgreSQL. When a query uses the JSON data type, it stores plain JSON data, which is parsed each time. The JSONB data type, on the other hand, is used to store JSON data in binary form. On the one hand, this permits indexing of table data, which speeds up data querying, but slows down the data insertion process as it supports indexing of table data.

UUID

The RFC 4122-defined Universal Unique Identifiers (UUIDs) can be stored using the UUID data type. The UUID values can be used to conceal sensitive data that is accessible to the public, such as the id values in URLs, and guarantee a higher level of uniqueness than SERIAL.

Unique Universal Identifiers is the name of the concept. These are employed to assign an exclusive ID to information that is singular across the database. The UUID of the data defined by RFC 4122 is stored using the UUID data type. These work better when compared to SERIAL data types in terms of uniqueness and are frequently used to protect sensitive data, such as credit card information.

Special data types:

PostgreSQL provides a few different data types that are connected to networks or geometrics in addition to the primitive data types. The following is a list of these unique data types: 

• box: It is used to store rectangular boxes.
• point: It is used to store geometric pairs of numbers.
• lseg: It is used to store line segments.
• point: It is used to store geometric pairs of numbers.
• polygon: It is used to store closed geometrics.
• inet: It is used to store an IP4 address.
• macaddr: It is used to store MAC addresses.

Monetary

The monetary data type stores a numeric amount of money with fixed fractional precision. This type stores up to 8 bytes of data with a range of -92233720368547758.08 to +92233720368547758.07 and use numeric integer and bigint data types as values.

Binary

PostgreSQL can save variable-length binary strings as the bytes data type, taking 1 or 4 bytes plus the size of the actual binary string.

Bit String

Bit string type stores strings of 1's and 0's, used to store or visualize bit masks:

Name

Description

bit(n)

Stores a bit string with a fixed length of n characters.

varying(n)

Stores a bit string of a varying length, up to n characters.

XML

PostgreSQL allows you to save XML data as an XML data type using the XMLPARSE function:

XMLPARSE (DOCUMENT [document name] WELLFORMED)

or:

XMLPARSE (CONTENT [XML content] WELLFORMED)

Where:

• [document name]: A singly-rooted XML document.
• [XML content]: Valid XML value
• WELLFORMED: This option guarantees that [document name] or [XML content] resolve to a well-formed XML document. Use it only when you don't want the database to check if the input is well-formed.

Composite

‍

Composite data types allow you to use a row or record of a table as a data element. Similar to array data types, you can also declare, search, and modify composite values

Object Identifier

PostgreSQL uses object identifiers as primary key systems when performing specialized input and output operations:

Name

References

Description

oid

any

Numeric object identifier.

regproc

pg_proc

Function name.

regprocedure

pg_proc

Function with argument types.

regoper

pg_operator

Operator name.

regoperator

pg_operator

Operator with argument types.

regclass

pg_class

Relation name.

regtype

pg_type

Data type name.

regconfig

pg_ts_config

Text search configuration.

regdictionary

pg_ts_dict

Text search dictionary.

Pseudo Types

Pseudo types are a collection of special entries for declaring a function's argument or result type:

Name

Description

any

Function accepts any input data type.

anyelement

Function accepts any data type.

anyarray

Function accepts any array data type.

anynonarray

Function accepts any non-array data type.

anyenum

Function accepts any enumerated data type.

anyrange

Function accepts any range data type.

cstring

Function accepts or return a null-terminated C string.

internal

Function accepts or return server-internal data type.

language_handler

Function returns language handler.

fdw_handler

A foreign-data wrapper handler returns fdw_handler.

record

Finds a function that returns an unspecified row type.

trigger

A trigger function returns the trigger.

void

Function returns no value.

Domain

A user-defined data type called a domain is one that is based on an underlying type. It may also include restrictions that limit the set of possible values for it to take to a subset of what the underlying type would permit. For example, any operator or function that can be applied to the underlying type will operate on the domain type. Otherwise, it behaves like the underlying type. The underlying type may be an enum type, array type, composite type, range type, built-in or user-defined base type, or it may belong to another domain.

For example, we could create a domain over integers that accepts only positive integers:

CREATE DOMAIN posint AS integer CHECK (VALUE > 0);

CREATE TABLE mytable (id posint);

INSERT INTO mytable VALUES(1);   -- works

INSERT INTO mytable VALUES(-1);  -- fails

When an operator or function of the underlying type is applied to a domain value, the domain is automatically down-cast to the underlying type. Thus, for example, the result of mytable.id - 1 is considered to be of type integer, not posint. We could write (mytable.id - 1)::posint to cast the result back to posint, causing the domain's constraints to be rechecked. In this case, that would result in an error if the expression had been applied to an id value of 1. Assigning a value of the underlying type to a field or variable of the domain type is allowed without writing an explicit cast, but the domain's constraints will be checked.

After reading this article, you should have a solid understanding of the different PostgreSQL data types.

‍

Related Articles:

MongoDB vs PostgreSQL: A Comparison

Installing PostgreSQL on a MacOS

How to Download, Install and Verify PostgreSQL for Windows