PostgreSQL Data Types

• This chapter discusses PostgreSQL Data Types While creating table, for each column, you specify a data type, i.e., what kind of data you want to store in the table fields.
• There are different categories of data types in PostgreSQL. They are discussed as below:

Numeric datatype:

• Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals.

Monetary Types:

• The money type stores a currency amount with a fixed fractional precision;
• The fractional precision is determined by the database’s lc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as ‘$1,000.00’. Output is generally in the latter form but depends on the locale.

Character Types:

• The table below lists general-purpose character types available in PostgreSQL.

Binary Data Types:

• The bytea data type allows storage of binary strings

Date/Time Types:

• PostgreSQL supports the full set of SQL date and time types,
• Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced

Boolean Type:

• PostgreSQL provides the standard SQL type boolean;
• The boolean type can have several states: “true”, “false”, and a third state, “unknown”, which is represented by the SQL null value.

Valid literal values for the “true” state are:
TRUE
‘t’
‘true’
‘y’
‘yes’
‘on’
‘1’
For the “false” state, the following values can be used:
FALSE
‘f’
‘false’
‘n’
‘no’
‘off’
‘0’

Leading or trailing whitespace is ignored, and case does not matter. The key words TRUE and FALSE are the preferred (SQL-compliant) usage.
Enumerated Types:

• Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages.
• An example of an enum type might be the days of the week, or a set of status values for a piece of data.

Examples:
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
name | current_mood
------+--------------
Moe | happy
(1 row)

Geometric Types:

• Geometric data types represent two-dimensional spatial objects
• A rich set of functions and operators is available to perform various geometric operations such as scaling, translation, rotation, and determining intersections

Network Address Types:

• PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses.
• It is better to use these types instead of plain text types to store network addresses, because these types offer input error checking and specialized operators and functions

Bit String Type:

• Bit String Types are used to store bit masks. They are either 0 or 1. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer.

Text Search Types:

• PostgreSQL provides two data types that are designed to support full text search, which is the activity of searching through a collection of natural-language documents to locate those that best match a query.
• The tsvector type represents a document in a form optimized for text search;
• the tsquery type similarly represents a text query.

UUID Types:

• A UUID (Universally Unique Identifiers) is written as a sequence of lower-case hexadecimal digits,
• In several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.

Example:ba0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML Types

• The xml data type can be used to store XML data. Its advantage over storing XML data in a text field is that it checks the input values for well-formedness,
•  There are support functions to perform type-safe operations on it;.
• Use of this data type requires the installation to have been built with configure –with-libxml.

JSON Types:

• JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159.
• Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules.
• There are also assorted JSON-specific functions and operators available for data stored in these data types

JSON primitive types and corresponding PostgreSQL types

Arrays Types:

• PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported.

Composite Types:

• A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types.
•  PostgreSQL allows composite types to be used in many of the same ways that simple types can be used.
• For example, a column of a table can be declared to be of a composite type.

Range Types:

• Range types are data types representing a range of values of some element type (called the range’s subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is tsrange (short for “timestamp range”), and timestamp is the subtype. The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.
• Range types are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly.
• The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.

PostgreSQL comes with the following built-in range types:

int4range — Range of integer

int8range — Range of bigint

numrange — Range of numeric

tsrange — Range of timestamp without time zone

tstzrange — Range of timestamp with time zone

daterange — Range of date

Object Identifier Types:

• Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.
• OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created, or the default_with_oids configuration variable is enabled.
• Type oid represents an object identifier. There are also several alias types for oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary

pg_lsn Type:

• The pg_lsn data type can be used to store LSN (Log Sequence Number) data which is a pointer to a location in the XLOG.
• This type is a representation of XLogRecPtr and an internal system type of PostgreSQL.
• Internally, an LSN is a 64-bit integer, representing a byte position in the write-ahead log stream. It is printed as two hexadecimal numbers of up to 8 digits each, separated by a slash; for example, 16/B374D848. The pg_lsn type supports the standard comparison operators, like = and >. Two LSNs can be subtracted using the – operator; the result is the number of bytes separating those write-ahead log positions.

Pseudo-Types:

• The PostgreSQL type system contains a number of special-purpose entries that are collectively called pseudo-types.
• A pseudo-type cannot be used as a column data type, but it can be used to declare a function’s argument or result type. Each of the available pseudo-types is useful in situations where a function’s behavior does not correspond to simply taking or returning a value of a specific SQL data type

Postgres Domain Data Type:

• user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint.
• domain will be check faster than primary key  you will understand from Domain Data Type Example