w3resource

PostgreSQL Data Types

Data Types

This document 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.

This enables several benefits:

Consistency: A column can can store a single type of value. So, when you select a column with integer type, you are sure that the result will have integer only.

Validation : A column can store different types of values. So, if a column's type is integer, you can not store string there.

Compactness: Since a column can store a single type of value, it is stored in a compact way.

Performance: Since type is uniform in a column, the values stored can be processed quickly, which enhances performance

A wide set of Data Types are available in PostgreSQL. Besides, users can create their own custom data type using "CREATE TYPE" command.

In the rest of the document, we have discussed each of the PostgreSQL Data Types based on PostgreSQL 9.1 Version.

Category - Numeric Types

Name Description Storage Size Range
smallint Stores whole numbers, small range. 2 bytes -32768 to +32767
integer Stores whole numbers.Use this when you want to store typical integers. 4 bytes -2147483648 to +2147483647
bigint Stores whole numbers, large range. 8 bytes -9223372036854775808 to 9223372036854775807
decimal user-specified precision, exact variable up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
numeric user-specified precision, exact variable up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
real variable-precision, inexact 4 bytes 6 decimal digits precision.
double precision variable-precision, inexact 8 bytes 15 decimal digits precision
serial auto incrementing integer 4 bytes 1 to 2147483647
bigserial large auto incrementing integer 8 bytes 1 to 9223372036854775807

Category - Monetary Types

Name Description Storage Size Range
money currency amount 8 bytes -92233720368547758.08 to +92233720368547758.07

Category - Character Types

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

Category - Binary Data Types

Name Description Storage Size
bytea variable-length binary string 1 or 4 bytes plus the actual binary string

Category - Date/Time Types

Name Description Storage Size Low Value High Value Resolution
timestamp [ (p) ] [ without time zone ] both date and time (no time zone) 8 bytes 4713 BC 294276 AD 1 microsecond / 14 digits
timestamp [ (p) ] with time zone both date and time, with time zone 8 bytes 4713 BC 294276 AD 1 microsecond / 14 digits
date date (no time of day) 4 bytes 4713 BC 5874897 AD 1 day
time [ (p) ] [ without time zone ] time of day (no date) 8 bytes 00:00:00 24:00:00 1 microsecond / 14 digits
time [ (p) ] with time zone times of day only, with time zone 12 bytes 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits
interval [ fields ] [ (p) ] 12 bytes time interval -178000000 years 178000000 years 1 microsecond / 14 digits

Category - Boolean Type

Name Description Storage Size
boolean state of true or false 1 byte

Category - Enumerated Type

Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values, for example compass directions,i.e. NORTH, SOUTH, EAST, and WEST or days of the week.

Code:

CREATE TYPE mood AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

Once creates, they can be used like any other types.

Category - Geometric Type

It represents two-dimensional objects pertaining to space.

Name Description Storage Size Representation
point Point on a plane 16 bytes (x,y)
line Infinite line (not fully implemented) 32 bytes ((x1,y1),(x2,y2))
lseg Finite line segment 32 bytes ((x1,y1),(x2,y2))
box Rectangular box 32 bytes ((x1,y1),(x2,y2))
path Closed path (similar to polygon) 16+16n bytes ((x1,y1),...)
path Open path 16+16n bytes [(x1,y1),...]
polygon Polygon (similar to closed path) 40+16n byte ((x1,y1),...)
circle circle 24 bytes <(x,y),r> (center point and radius)

Category - Network Address Type

These Data Type stores store IPv4, IPv6, and MAC addresses.

Name Description Storage Size
cidr IPv4 and IPv6 networks 7 or 19 bytes
inet IPv4 and IPv6 hosts and networks 7 or 19 bytes
macaddr MAC addresses 6 bytes

Category - Bit String Type

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

Category - Text Search Type

Used for Full Text Search. There are two Data Types for this -

tsvector: Which is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as "lexemes".

tsquery: A tsquery value stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators.

Category - UUID Type

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. An example of a UUID is

550e8400-e29b-41d4-a716-446655440000

Category - XML Type

The xml data type can be used to store XML data. For storing XML data, first you create XML values using function xmlparse.

Code:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title><topics>...</topics></tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Category - Array Type

In PostgreSQL, it is possible to define a column of a table as a variable length multidimensional array. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. But Arrays of domains are not yet supported.

Declaration of Arrays

Code:

CREATE TABLE monhly_savings (
name text,
saving_per_quarter  integer[],
scheme text[][]);

Inserting values

Code:

INSERT INTO monhly_savings
VALUES ('Bidhan',
'{20000, 14600, 23500, 13250}', '{{"FD", "MF"}, {"FD", "Property"}}');

Accessing Arrays

Code:

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4]; 

So, the above command will select persons whose savings are more in second quarter that fourth quarter.

Category - Composite Types

This type represents a list of field names and their data types, i.e. structure of a row or record of a table.

Category - Object Identifier Types

Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.

Name Description References Value Example
oid numeric object identifier any 564182
regproc function name pg_proc sum
regprocedure function with argument types pg_proc sum(int4)
regoper operator name pg_operator +
regoperator operator with argument types pg_operator *(integer,integer) or -(NONE,integer)
regclass relation name pg_class pg_type
regtype data type name pg_type integer
regconfig text search configuration pg_ts_config english
regdictionary text search dictionary pg_ts_dict simple

Category - Pseudo Types

Name Description
any Denotes that a function accepts any input data type.
anyarray Denotes that a function accepts any array data type
anyelement Denotes that a function accepts any data type
anyenum Denotes that a function accepts any enum data type
anynonarray Denotes that a function accepts any non-array data type
cstring Denotes that a function accepts or returns a null-terminated C string
internal Denotes that a function accepts or returns a server-internal data type.
language_handler A procedural language call handler is declared to return language_handler.
fdw_handler A foreign-data wrapper handler is declared to return fdw_handler.
record Denotes a function returning an unspecified row type.
trigger A trigger function is declared to return trigger.
void Denotes that a function returns no value.
opaque An obsolete type name that formerly served all the above purposes.

Previous: PostgreSQL Connector and APIs
Next: Create Database



Follow us on Facebook and Twitter for latest update.