Log In to start Learning

Login via

Post By Admin Last Updated At 2020-06-15
PostgreSQL Data Types

The PostgreSQL is a database that holds different values of data. So in this tutorial let have a look over the PostgreSQL data types.  But before  we were going to discuss the PostgreSQL supported data types, I would like to  recall you once again

What is the data type?

A data type (or) simply types refers to the specific type of data that the variable holds. Moreover, this include date, timestamps, varchar, and some other formats.

Let us have a look at the PostgreSQL datatypes.

Numeric Datatypes :

PostgreSQL supports two types of Numeric datatypes namely integers and Floating point numbers. So let us now discuss the PostgreSQL supports types with range.

NameStorage size Range
Small2 bytes-32768 - +32767
Integer4 bytes-2147483648 to +2147483647
Bigint8 bytes-9223372036854775808 to 9223372036854775807
Real4 bytesIt support 06 digits precision
Double precision8 bytesIt supports 15digit decimal precision
DecimalVariable It permits up to 131072 before the decimal point, up to 16383 after the decimal point
NumericVariableIt permits up to 131072 before the decimal point, up to 16383 after the decimal point
Small serial2 bytes1 – 32767
Serial4 bytes1 - 2147483647
Double serial8 bytes1 to 9223372036854775807

 Monetary data types :

This is the special data that the database supports. Here in this data type, values of numeric, int and bigint can be converted into money. So  today many people say that we can use Float datatype. But this is not recommended to handle money due to the potential for rounding errors.

Money8 bytes-92233720368547758.08 to +92233720368547758.07

Character data types:

The following table contains the PostgreSQL character datatypes.

Char(size)/ Character(size)Here size is the number of characters to store. It contains the fixed length strings. Here the space padded on right to equal size characters.
Varchar(size)Here size is the number of characters to store. It contains the variable string length

Date/time data type:

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

Timestamp(with/ without timezone)8 bytes4713 BC to 294276 AD1 microsecond / 14 digits
Date4bytes4713 BC to 294276 ADOne day
Time without time zone8 bytes00:00:00 to 24:00:001 microsecond /14 digits
Timewith timezone12 bytes00:00:00 + 1459 to 24:00:00-1459I microsecond/ 14 digits
Interval12bytes-178000000 to 178000000 yearsI microsecond/14 digits

Geometric data types :

The geometric data types represent two-dimensional data objects.  Moreover these data types help to perform a various operation like rotation, scaling and translation etc.

Point16 bytesPoint on a plane(x,y)
Line32 bytesIinfinte line((x1.y1).(x2.y2))
Line segment32 bytesFinite line segment((x1.y1).(x2.y2))
Box32 bytesRectangular box((x1.y1).(x2.y2))
Path16 n + 16 n bytesClose and open path((x1.y1),…..)
Polygon40 + 16 n bytesPolygon[(x1.y1)…]
Circle24 bytesCircle<(x.y).r>

Network Address type:

Today we do have many network address types like IPV4, IP V6, and MAC address. Moreover, this PostgreSQL offers different data types to store in these address. To store in network address, instead of plain text,  it is preferable to store these data types.

NameStorage sizeDescription
Cidr7 (or) 19 bytesIPV4 and IPV6 networks
Inet7 (or) 19 bytesIPV4 and IPV 6 hosts and networks
Macaddr6 bytesMac address

Enumerated data types:

In PostgreSQL, enumerated data types are useful for representing the rarely changing information such as country code (or) branch id. so  to ensure data integrity, the enumerated data type is represented with a table with a foreign key.

Range type :

These represent data that uses the data range. These range types can be a discrete range (or) continuous ranges. Postgre SQL contains the following built-in data types.

Int4rangeRange of the integer
Int8rangeRange of the big int
NumrangeRange of the numeric
tsrangeWithout timezone, time stamp range
tstzrangeWith timezone, time stamp range
Date rangeIt represents the range of the date

Pseudo types :

PostgreSQL contains a number of special purpose entries that are collectively called as pseudo types.  Moreover , a  pseudo data type cannot be used as a column data type that can be used as a column data type. Additionally, this can be used to declare a function argument (or) a result type.

AnyIt indicates the function accepts any input data type
Any elementIt indicates the function accepts any  data type
Any arrayIt indicates the function accepts any array data type
AnynonarrayIt indicates the function accepts any non-array data type
AnyenumIt indicates the function accepts any enum data type
AnyrangeIt indicates the function accepts any range data type.
InternalIt indicates the function accepts (or) returns an internal server data.
RecordIt indicates the function returning an unspecified row type
VoidIt indicates the function that has no value
CStringIt indicates the function accepts (or) returns the Null-terminated C string