PostgreSQL DataTypes

 

Numeric Types:

Name	Storage Size	Range
smallint	2 bytes	-32768 to +32767
integer	4 bytes	-2147483648 to +2147483647
bigint	8 bytes	-9223372036854775808 to 9223372036854775807
decimal	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real	4 bytes	6 decimal digits precision
double precision	8 bytes	15 decimal digits precision
smallserial	2 bytes	1 to 32767
serial	4 bytes	1 to 2147483647
bigserial	8 bytes	1 to 9223372036854775807

 Monetary Types:

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

Character Types:

S. No.	Name & Description
1	character varying(n), varchar(n) variable-length with limit
2	character(n), char(n) fixed-length, blank padded
3	text variable unlimited length

 Binary Data Types:

Name	Storage Size
bytea	1 or 4 bytes plus the actual binary string

Date/Time Types:

Name	Storage Size	Description
timestamp [(p)] [without time zone ]	8 bytes	both date and time (no time zone)
TIMESTAMPTZ	8 bytes	both date and time, with time zone
date	4 bytes	date (no time of day)
time [ (p)] [ without time zone ]	8 bytes	time of day (no date)
time [ (p)] with time zone	12 bytes	times of day only, with time zone
interval [fields ] [(p) ]	12 bytes	time interval

Boolean Type:

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

Geometric Type:

Name	Storage Size	Description
point	16 bytes	(x,y)
line	32 bytes	((x1,y1),(x2,y2))
lseg	32 bytes	((x1,y1),(x2,y2))
box	32 bytes	((x1,y1),(x2,y2))
path	16+16n bytes	((x1,y1),...)
path	16+16n bytes	[(x1,y1),...]
polygon	40+16n	((x1,y1),...)
circle	24 bytes	<(x,y),r> (center point and radius)

Network Address Type:

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

Text Search Type:

S. No.	Name & Description
1	tsvector This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as "lexemes".
2	tsquery This 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.

UUID Type (Universally Unique Identifiers)

Example:

UUID  − 550e8400-e29b-41d4-a716-446655440000

XML Type:

Example:

XMLPARSE (DOCUMENT '<?xml version="1.0"?>

<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

 JSON Type:

        The json data type can be used to store JSON (JavaScript Object Notation) data. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value.

Example	Example Result
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,'foo'))	{"f1":1,"f2":"foo"}

  

ARRAY  Type:

Example:

CREATE TABLE monthly_savings (

   name text,

   saving_per_quarter integer[],

   scheme text[][]

);

Composite Types(Create Type):

Example:

CREATE TYPE inventory_item AS (

   name text,

   supplier_id integer,

   price numeric

);

CREATE TABLE on_hand (

   item inventory_item,

   count integer

);

Range Types:

Example:

The built-in range types available include the following ranges −

    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.

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:

        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.

S. No.	Name & Description
1	any Indicates that a function accepts any input data type.
2	anyelement Indicates that a function accepts any data type.
3	anyarray Indicates that a function accepts any array data type.
4	anynonarray Indicates that a function accepts any non-array data type.
5	anyenum Indicates that a function accepts any enum data type.
6	anyrange Indicates that a function accepts any range data type.
7	cstring Indicates that a function accepts or returns a null-terminated C string.
8	internal Indicates that a function accepts or returns a server-internal data type.
9	language_handler A procedural language call handler is declared to return language_handler.
10	fdw_handler A foreign-data wrapper handler is declared to return fdw_handler.
11	record Identifies a function returning an unspecified row type.
12	trigger A trigger function is declared to return trigger.
13	void Indicates that a function returns no value.