Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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. 

Storage Size
2 bytes
small-range integer
-32768 to +32767
4 bytes
typical choice for integer
-2147483648 to +2147483647
8 bytes
large-range integer
-9223372036854775808 to +9223372036854775807
user-specified precision, exact
up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
user-specified precision, exact
up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
4 bytes
variable-precision, inexact
6 decimal digits precision
8 bytes
variable-precision, inexact
15 decimal digits precision
2 bytes
small autoincrementing integer
1 to 32767
4 bytes
autoincrementing integer
1 to 2147483647
8 bytes
large autoincrementing integer
1 to 9223372036854775807

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.
Storage Size
8 bytes
currency amount
-92233720368547758.08 to +92233720368547758.07

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

character varying(n), varchar(n)
variable-length with limit
character(n), char(n)
fixed-length, blank padded
variable unlimited length

Binary Data Types:
  • The bytea data type allows storage of binary strings
Storage Size
1 or 4 bytes plus the actual binary string
variable-length binary string

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 
Storage Size
Low Value
High Value
timestamp [ (p) ] [ without time zone ]
8 bytes
both date and time (no time zone)
4713 BC
294276 AD
1 microsecond / 14 digits
timestamp [ (p) ] with time zone
8 bytes
both date and time, with time zone
4713 BC
294276 AD
1 microsecond / 14 digits
4 bytes
date (no time of day)
4713 BC
5874897 AD
1 day
time [ (p) ] [ without time zone ]
8 bytes
time of day (no date)
1 microsecond / 14 digits
time [ (p) ] with time zone
12 bytes
times of day only, with time zone
1 microsecond / 14 digits
interval [ fields ] [ (p) ]
16 bytes
time interval
-178000000 years
178000000 years
1 microsecond / 14 digits

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.
Storage Size
1 byte
state of true or false

Valid literal values for the "true" state are:
For the "false" state, the following values can be used:

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.
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
    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
Storage Size
16 bytes
Point on a plane
32 bytes
Infinite line
32 bytes
Finite line segment
32 bytes
Rectangular box
16+16n bytes
Closed path (similar to polygon)
16+16n bytes
Open path
40+16n bytes
Polygon (similar to closed path)
24 bytes
<(x,y),r> (center point and radius)

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
Storage Size
7 or 19 bytes
IPv4 and IPv6 networks
7 or 19 bytes
IPv4 and IPv6 hosts and networks
6 bytes
MAC addresses

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.

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
JSON primitive type
PostgreSQL type
\u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8
NaN and infinity values are disallowed
Only lowercase true and false spellings are accepted
SQL NULL is a different concept

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.
  • 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
Indicates that a function accepts any input data type.
Indicates that a function accepts any data type
Indicates that a function accepts any array data type
Indicates that a function accepts any non-array data type
Indicates that a function accepts any enum data type
Indicates that a function accepts any range data type
Indicates that a function accepts or returns a null-terminated C string.
Indicates that a function accepts or returns a server-internal data type.
A procedural language call handler is declared to return language_handler.
A foreign-data wrapper handler is declared to return fdw_handler.
A tablesample method handler is declared to return tsm_handler.
Identifies a function taking or returning an unspecified row type.
A trigger function is declared to return trigger.
An event trigger function is declared to return event_trigger.
Identifies a representation of DDL commands that is available to event triggers.
Indicates that a function returns no value.
An obsolete type name that formerly served all the above purposes.


Popular posts from this blog

7 Steps to configure BDR replication in postgresql

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?


How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory