Let’s study PostgreSQL data types in detail

Text Types Numeric Types Dates and Times XML JSON Boolean Bits Binary Data Network Arrays Create your Data Type Boolean Temporal UUID Array JSON Special Data types for storing a network address and geometric data.

Character Datatypes Numeric Datatypes Binary Data Types Network Address Type Text Search Type Date/Time Datatypes Boolean Type Geometric Data Types Enumerated Types Range Type UUID type XML type JSON Type Pseudo-Types

Character Datatypes

PostgreSQL supports character data types for storing text values. PostgreSQL builds character data types off of the same internal structures. PostgreSQL offers three character data types: CHAR(n), VARCHAR(n), and TEXT.

Numeric Datatypes

PostgreSQL supports two distinct types of numbers:

Integers Floating-point numbers

Binary Data Types

A binary string is a sequence of octets or bytes. Binary Postgres Data Types are divided in two ways.

Binary strings allow storing odds of value zero Non- printable octets

Character strings not allow zero octets and also disallows any other octet values and sequences which are invalid as per the database’s character set encoding rules.

Network Address Type

Many applications store network information like IP addresses of users or sensors. PostgreSQL has three native types which help you to optimize the network data. Using Network Address Types has following advantages

Storage Space Saving Input error checking Functions like searching data by subnet

Text Search Type

PostgreSQL provides two data types which are designed to support full-text search. Full-text search is searching through a collection of natural-language documents to search those that best match a query.

Tsvector text search PostgreSQL variable types represent a document in a form optimized for text search The query type text search stores the keywords that need to be searched

Date/Time Datatypes

PostgreSQL timestamp offers microsecond precision instead of second precision. Moreover, you also have the option of storing with timezone or without. PostgreSQL will convert timestamp with timezone to UTC on input and store it. Date and time input is accepted in various formats, including traditional Postgres, ISO 8601. SQL-compatible etc. PostgreSQL supports Day / Month / Year ordering. Formats supported are DMY, MDY, YMD

Temporal Data Types

Examples:

Time/ Time with Time Zone Input

Boolean Type

A Boolean data type can hold

True False null

values. You use a bool or boolean keyword to declare a column with the Boolean data type. When you insert values into a boolean column, Postgre converts values like

Yes y 1 t true

into 1. While values like

No N 0 F False

are converted to 0 While selecting data, the values are again converted back to yes, true, y, etc.

Geometric Data Types

Geometric data types represent two-dimensional spatial objects. They help perform operations like rotations, scaling, translation, etc.

Enumerated Types

Enumerated PostgreSQL data type is useful for representing rarely changing information such as country code or branch id. The Enumerated data type is represented in a table with foreign keys to ensure data integrity.

Example:

Hair color is fairly static in a demographic database

Range Type

Many business applications require data in ranges. Typically, two columns (example: start date, end date) are defined to deal with ranges. This is both inefficient and difficult to maintain. Postgre has built range types as follows

int4range — Display range of integer
int8range — Display range of bigint
numrange — Shows the numeric range
tstrange — Helps you to display timestamp without time zone
strange — Allows you to display timestamp with time zone
date range — Range of date

UUID type

Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm. It is very unlikely that the same identifier will be generated by another person in the world using the same algorithm. That’s why for the distributed systems, these identifiers are an ideal choice as it offers uniqueness within a single database. A UUID is written as a group of lower-case hexadecimal digits, with various groups separated by hyphens. PostgreSQL has a native UUID data type which consumes 16 bytes of storage.. UUID is an ideal Data type for primary keys.

Example:

Postgre also accepts alternative forms of UUID inputs like all capital case, no hyphens, braces, etc.

XML type

PostgreSQL allows you to store XML data in a data type, but it is nothing more than an extension to a text data type. But the advantage is that it checks that the input XML is well-formed.

Example:

JSON Type

To store JSON data PostgreSQL offers 2 data types

JSON JSONB

Most widely used JSON data type used us jsonb unless there is some specialized need to use JSON data type.

Example:

Pseudo-Types

PostgreSQL has many special-purpose entries that are called pseudo-types. You can’t use pseudo-types as PostgreSQL column types. There are used to declare or function’s argument or return type. Each of the available pseudo-types is helpful in situations where a function’s behavior docs do not correspond to simply taking or returning a value of a specific SQL data type. It is important that the user who is using this function need to make sure that the function will behave securely when a pseudo-type is used as an argument type.

Best practices using Data types

Use “text” data type unless you want to limit the input Never use “char.” Integers use “int.” Use bigint only when you have really big numbers Use “numeric” almost always Use float in PostgreSQL if you have IEEE 754 data source

Summary

PostgreSQL offers a rich set of native data types for users PostgreSQL supports character data types for storing text values PostgreSQL supports two distinct types of numbers: 1. Integers, 2. Floating-point numbers A binary string is a sequence of bytes or octets PostgreSQL has Network address type to help you optimize storage of network data Text search PostgreSQL data structures are designed to support full-text search Date/Time PSQL data types are allow date and time information in various formats Boolean Postgres field types can hold three values 1. True 2. False 3. Null Geometric PostgreSQL data types represent two-dimensional spatial objects Enumerated Data types in PostgreSQL is useful for representing rarely changing information such as country code or branch id Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm PostgreSQL has many special-purpose entries that are called pseudo-types It is best practice to use “text” data type unless you want to limit the input