Javatpoint Logo
Javatpoint Logo

PostgreSQL Data Types

A datatype specifies, what kind of data you want to store in the table field. While creating table, for each column, you have to use a datatype.

There are mainly three types of datatypes in PotgreSQL. Besides this, users can also create their own custom datatypes using CREATE TYPE SQL command.

Following are the mainly three types of datatypes in PostgreSQL:

  • Numeric datatype
  • String datatype
  • Date/time datatype

Numeric Data Type

Numeric datatype is used to specify the numeric data into the table.

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

String Data Type

String datatype is used to represent the string type values.

Datatype Explanation
char(size)
Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
character(size) Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
varchar(size) Here size is the number of characters to store. Variable-length string.
character varying(size) Here size is the number of characters to store. Variable-length string.
text Variable-length string.

Date/Time Data Type

The date/time datatype is used to represent the columns using date and time values.

Name Description Storage size Minimum value Maximum 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) ] time interval 12 bytes -178000000 years 178000000 years 1 microsecond / 14 digits

Some other data Types

Boolean type:

Name Description Storage size
boolean it specifies the state of true or false. 1 byte

Monetary type:

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

Geometric Type:

Geometric data types represent two-dimensional spatial objects. The most fundamental type, the point, forms the basis for all of the other types.

Name Storage size Representation Description
point 16 bytes point on a plane (x,y)
line 32 bytes infinite line (not fully implemented) ((x1,y1),(x2,y2))
lseg 32 bytes finite line segment ((x1,y1),(x2,y2))
box 32 bytes rectangular box ((x1,y1),(x2,y2))
path 16+16n bytes closed path (similar to polygon) ((x1,y1),...)
path 16+16n bytes open path [(x1,y1),...]
polygon 40+16n polygon (similar to closed path) ((x1,y1),...)
circle 24 bytes circle <(x,y),r> (center point and radius)

Next TopicInstall PostgreSQL




Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA