PostgreSQL Data Types

PostgreSQL support extensive list of Data Types. Below given different data type categories. Out of these extensive list of data types given below Numeric, Character and Data/Time Data Types are the most commonly used ( We can say roughly 60%-80% of the data types falls under any or all of these three categories )

 

Numeric Types
Character Types
Date/Time Types

Binary Data Types
Monetary Types
Boolean Type
Enumerated Types
Geometric Types
Network Address Types
Bit String Types
Text Search Types
UUID Type
XML Type
JSON Types
Arrays
Composite Types
Range Types
Domain Types
Object Identifier Types
pg_lsn Type
Pseudo-Types

Name Description Data Type Category Alias
bytea binary data (“byte array”) BINARY  
bit[(n)] fixed-length bit string BIT STRING  
char[(n)] fixed-length character string CHARACTER  
varchar[(n)] variable-length character string CHARACTER  
text variable-length character string CHARACTER  
date calendar date (year, month, day) DATE and TIME  
time [(p)] [without time zone] time of day (no time zone) DATE and TIME  
time [(p)] [with time zone] time of day, including time zone DATE and TIME timetz
timestamp [(p)] [without time zone] date and time (no time zone) DATE and TIME  
timestamp [ (p)] [with time zone] date and time, including time zone DATE and TIME  
line infinite line on a plane GEOMETRIC  
lseg line segment on a plane GEOMETRIC  
path geometric path on a plane GEOMETRIC  
point  geometric point on a plane GEOMETRIC  
polygon closed geometric path on a plane GEOMETRIC  
json textual JSON data JSON  
jsonb binary JSON data, decomposed JSON  
money currency amount MONETARY  
box rectangular box on a plane NETWORK  
cidr IPv4 or IPv6 network address NETWORK  
circle circle on a plane NETWORK  
inet IPv4 or IPv6 host address NETWORK  
macaddr MAC (Media Access Control) address NETWORK  
macaddr8 MAC (Media Access Control) address NETWORK  
bigint signed eight-byte integer NUMERIC  
bigserial autoincrementing eight-byte integer NUMERIC  
varbit[(n)] variable-length bit string NUMERIC  
bool  logical Boolean (true/false) NUMERIC  
float 8 double precision floating-point number NUMERIC  
int signed four-byte integer NUMERIC  
interval [ fields ] [ (p) ] time span NUMERIC  
decimal[(p, s)] exact numeric of selectable precision NUMERIC numeric[(p, s)]
real single precision floating-point number  NUMERIC float4
smallint signed two-byte integer NUMERIC int2
smallserial  autoincrementing two-byte integer NUMERIC serial2
serial  autoincrementing four-byte integer NUMERIC serial4
pg_lsn  PostgreSQL Log Sequence Number PG_LSN  
pg_snapshot  user-level transaction ID snapshot PG_LSN  
tsquery text search query TEXT SEARCH  
tsvector text search query TEXT SEARCH  
uuid universally unique identifier UUID  
xml XML data XML  

Numeric Data Types

Numeric types are basically integer and decimal types, integer for storing full integers and decimals are for storing decimal numbers

NameStorage SizeDescriptionRange
smallint2 bytessmall-range integer-32768 to +32767
integer4 bytestypical choice for integer“-9223372036854775808 to +9223372036854775807”
bigint8 byteslarge-range integer-9.22337E+18
decimalvariableuser-specified precision, exactup to 131072 digits before
the decimal point; up to
16383 digits after the decimal
point
numericvariableuser-specified precision,
exact
up to 131072 digits before
the decimal point; up to
16383 digits after the decimal
point
real4 bytesvariable-precision, inexact6 decimal digits precision
double precision8 bytesvariable-precision, inexact15 decimal digits precision
smallserial2 bytessmall autoincrementing integer1 to 32767
serial4 bytesautoincrementing integer1 to 2147483647
bigserial8 byteslarge autoincrementing integer1 to 9223372036854775807

Please consider the length or range of the field before you finalize the data type, say, “int” type can only store value max upto 32767

Character Data Types

There are three data types, varchar, char and text char – When you know the length of the text storing and it is never going to change you can use this varchar – When you know the max limit of the string and if string length vary text – When you have no idea about the max limit of the string

NameDescription
varchar(n)variable-length with limit
char(n)fixed-length, blank padded
textvariable unlimited length

See Below example

postgres=# create table test

 (

col1 char(3),

col2 varchar(3)

 );

CREATE TABLE


postgres=# insert into test values('a  ','a  ');

INSERT 0 1


postgres=# select col1,char_length(col1),col2,char_length(col2) from test;

 col1 | char_length | col2 | char_length 

------+-------------+------+-------------

 a    |           1 | a    |           3

(1 row)

Char type use only 1 character length while varchar uses 3 character length

Date and Time Data Types

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: