Data types in PosgreSQL


In this tutorial we will go through most common and most useful datatypes in PostgreSQL. There are dozens of different types and we will cover the most important ones that are used in 99% of cases.

First datatype I want to cover is INT. It stores whole numbers such as number of years, number of apples, number of centimeters, etc. It doesn’t support decimal numbers, only whole numbers, like 1,2 ,3, 4….

Next datatype is NUMERIC. It stores decimal numbers such as price (20.50), height in feet(5.5), etc.  When you define a NUMERIC datatype, you define it like this: NUMERIC(P,S). ‘P’ represents a total amount of numbers, and ‘S’ represents amount of numbers that come after decimal place. So, if we had a number 2.23, it would be defined like NUMERIC(3,2). If we had a number 222.333, it would be defined as NUMERIC(6,3)

Next datatype is SERIAL and are specific to PostgreSQL and are not true datatype like INT or NUMERIC. The SERIAL actualy holds integers, but these integers will get incremented for one with each new row added to table. It usualy stores ID values.

Next three datatypes we will see are String datatypes. First one is CHAR(N) datatype and it holds Strings of fixed length and fixed length is N.

Next is one of the most common datatypes you will see and it is a VARCHAR(N) datatype. It holds Strings of varius length with maximum length of N.

Next datatype is TEXT datatype holds varying length Strings with no maximum length. It usualy stores Strings like comments, reviews, etc.

Next three datatypes are related to time values. First datatype is TIME and it stores hours, minutes and seconds, like this HH:MM:SS

Next we have DATE datatype and it stores dates in format of YYYY-MM-DD.

Next datatype is TIMESTAMP which combines is combination of DATE and TIME. It stores values in format of YYYY-MM-DD HH:MM:SS

Last two datatypes we will see are BOOLEAN and ENUM.

BOOLEAN stores values in format of True or False and it represents if something is true or not. For example, it can store informations whether is certain article available or not.

Finaly ENUM datatype. It holds only whole values which are defined by the user. So the user defines a list of values and the column that has ENUM datatype asigned to it can only hold values which are in this list. So you could create a list of the week days and only these values could enter into the column. For example, if you would want your gender column to  only be able to hold two values of M and F, you would define a list with M and F and only these two values could be entered.

Share with your friends