PostgreSQL Data Types

Click to rate this post!
[Total: 0 Average: 0]

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.

Name Storage size Range
Small 2 bytes -32768 – +32767
Integer 4 bytes -2147483648 to +2147483647
Bigint 8 bytes -9223372036854775808 to 9223372036854775807
Real 4 bytes It support 06 digits precision
Double precision 8 bytes It supports 15digit decimal precision
Decimal Variable  It permits up to 131072 before the decimal point, up to 16383 after the decimal point
Numeric Variable It permits up to 131072 before the decimal point, up to 16383 after the decimal point
Small serial 2 bytes 1 – 32767
Serial 4 bytes 1 – 2147483647
Double serial 8 bytes 1 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.

Name Storage Range
Money 8 bytes -92233720368547758.08 to +92233720368547758.07

Character data types:

The following table contains the PostgreSQL character datatypes.

Name Description
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.

Name size Range Resolution
Timestamp(with/ without timezone) 8 bytes 4713 BC to 294276 AD 1 microsecond / 14 digits
Date 4bytes 4713 BC to 294276 AD One day
Time without time zone 8 bytes 00:00:00 to 24:00:00 1 microsecond /14 digits
Timewith timezone 12 bytes 00:00:00 + 1459 to 24:00:00-1459 I microsecond/ 14 digits
Interval 12bytes -178000000 to 178000000 years I 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.

Name Storage Representation Description
Point 16 bytes Point on a plane (x,y)
Line 32 bytes Iinfinte line ((x1.y1).(x2.y2))
Line segment 32 bytes Finite line segment ((x1.y1).(x2.y2))
Box 32 bytes Rectangular box ((x1.y1).(x2.y2))
Path 16 n + 16 n bytes Close and open path ((x1.y1),…..)
Polygon 40 + 16 n bytes Polygon [(x1.y1)…]
Circle 24 bytes Circle <(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.

Name Storage size Description
Cidr 7 (or) 19 bytes IPV4 and IPV6 networks
Inet 7 (or) 19 bytes IPV4 and IPV 6 hosts and networks
Macaddr 6 bytes Mac 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.

Name Description
Int4range Range of the integer
Int8range Range of the big int
Numrange Range of the numeric
tsrange Without timezone, time stamp range
tstzrange With timezone, time stamp range
Date range It 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.

Name Description
Any It indicates the function accepts any input data type
Any element It indicates the function accepts any  data type
Any array It indicates the function accepts any array data type
Anynonarray It indicates the function accepts any non-array data type
Anyenum It indicates the function accepts any enum data type
Anyrange It indicates the function accepts any range data type.
Internal It indicates the function accepts (or) returns an internal server data.
Record It indicates the function returning an unspecified row type
Void It indicates the function that has no value
CString It indicates the function accepts (or) returns the Null-terminated C string

to our newsletter

Drop Us A Query

Trending Courses
  • Microsoft Azure Architect Technologies(AZ-300) | ITGuru
    Microsoft Azure Architect Technologies(AZ-300)
  • Azure is a great Microsoft Cloud Computing platform in providing various cloud services through online. ITGuru Certified Azure Architect certification course gives you the practical knowledge on Azure Cloud platform through real-world use cases from live experts

  • ServiceNow Admin Training | ITGuru
    ServiceNow Admin Training
  • Getting knowledge of cloud platforms like ServiceNow is essential in today’s world for the smooth running of projects in cloud platform. Turn your dream to the reality of becoming the Certified ServiceNow Administrator through ServiceNow Administration online certification Course with practical examples by live industry experts through online at ITGuru with real-world use cases.

  • Workday Financials Training | ITGuru
    Workday Financials Training
  • knowing the basics on any platform like Workday is not enough to sustain the IT industry. Hence it is essential to go beyond on Workday basics like Workday Financials training which lets you know the application of Financials management in real -world use cases from ITGuru Live Experts in a practical way.

  • Workday Training
  • An organization is considered as the best one when it offers the best benefits to the employee. Moreover, the greater the employee benefits, the greater the contribution to the organization. ITGuru let you know the practical workday Human Resource Management(HRM) features with live examples by experts

  • Python Online Course | ITGuru
    Python Online Course
  • Turn your dream into reality by ITGuru live experts with real-world use cases through practical knowledge on python online course and become the certified associate in python programming and become a master in python programming

  • Python Programming | ITGuru
    Python Programming
  • Python is the trending programming language in the IT industry. Mastering in python programming gives you more value among the people in the IT industry. Hence start today to learn python programming online by live experts with real-time uses cases at ITGuru

100% Secure Payments. All major credit & debit cards accepted.

Call Now Button