Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

postgresql
communitycreator

What are the data types in PostgreSQL?

Feza Roheel

PostgreSQL is an open-source, object-relational database management system. It comprises of both object and relational models.

The datatypes in PostgreSQL can be categorized into:

  1. Built-in
  2. User-defined

1. Built-in data types

Built-in datatypes are primitive data types.

a. Numeric datatypes

Numeric datatypes include Integer types, Arbitrary precision numbers, Floating point numbers, and Serial types.

Integer types are whole numbers without any fractional or decimal part.

Name Size Range
smallint 2 bytes -32768 to +32767
integer 4 bytes -2147483648 to +2147483647
bigint 8 bytes -9223372036854775808 to +9223372036854775807

Arbitrary precision numbers are used where precision and exactness are required.

We can use the format NUMERIC ( precision, scale ). For example, 20.143 can be specified as NUMERIC(5,3) where total digitsi.e., precision is 5 and digits after the decimal pointi.e., scale is 3.

Name Size Range
decimal variable Before the decimal point: at most 131072 digits; after decimal point: at most 16383 digits
numeric variable Before the decimal point: at most 131072 digits; after decimal point: at most 16383 digits

Floating point numbers also include decimal numbers, but without exactness or precision. They determine the maximum precision and are dependent on the architecture of the system.

Name Size Range
real 4 bytes precision of 6 decimal digits
double precision 8 bytes precision of 15 decimal digits

Serial types are special types of integers that have the auto-increment property used to create unique identifiers.

Name Size Range
smallserial 2 bytes 1 to 32767
serial 4 bytes 1 to 2147483647
bigserial 8 bytes 1 to 9223372036854775807

b. Monetary type

The money type is typically used to store currency with a precision of the fractional part fixed. Input is accepted as floating-point literals, currency, and integers.

Name Size Range
money 8 bytes -92233720368547758.08 to +92233720368547758.07

c. Character types

Fixed length and variable length characters and strings can be stored in character types.

Name Description
varchar(n) variable length with n chars as limit
char(n) fixed length of n chars
text unlimited variable length

d. Date & time types

As indicated by the name, these are used to store the date and time.

Name Size
timestamp [ ( p ) ] [without time zone] 8 bytes
timestamp [ ( p ) ] with time zone 8 bytes
date 4 bytes
time [ ( p ) ] [without time zone] 8 bytes
time [ ( p ) ] with time zone 12 bytes
interval [ fields ] [ ( p ) ] 16 bytes

fields must include SECONDS. The range of precision i.e. p is from 0 to 6.

e. Boolean type

Boolean Type is used to store the states of True, False, or Null.

Name Size
boolean 1 byte

f. UUID type

Uuid is a 16-byte number used to uniquely identify some information. This type is a 32-digit number with the format:

[8 digits]-[4 digits]-[4 digits]-[4 digits]-[12 digits]

g. Arrays

Array is created of either built-in or user-defined datatype.

Multidimensional arrays of variable lengths can also be defined as columns for a table in PostgreSQL.

even_numbers  integer ARRAY,
Definition of 1-Dimensional Array

h. JSON types

json stores data in the same format, but jsonb remove the whitespaces, does not maintain key ordering, and stores the data in binary for faster processing.

i. XML type

XML standard documents and content fragments are stored in the XML Type.

XMLPARSE ( { DOCUMENT | CONTENT } value);
Syntax of creating an XML value

j. Other built-in types

There are many other special data types related to network, geometric, etc. Some examples include box, line, point, polygon, inet and macaddr.

2. User-defined data type

User-defined data types include non-primitive data types created by the user by combining primitive data types.

CREATE TYPE creates a new user-defined data type in the database.

a. Enumerated Type

Enumerated Type includes ordered and static sets of values.

CREATE TYPE weekdays AS ENUM ( 'monday', 'tuesday', 'wednesday', 'thursday', 'friday' );

b. Other user-defined types

There are many other user-defined data types such as Composite type, Range type, Base type, and Array Type.

RELATED TAGS

postgresql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring