Summary: in this tutorial, you will learn about PostgreSQL data types including Boolean, character, number, temporal, special types and array.
Overview of PostgreSQL data types
PostgreSQL supports the following data types:
- Boolean
- Character types such as
char
,varchar
, andtext
. - Number such as numeric, integer, etc.
- Temporal i.e., date, time, timestamp, and interval
- Array
- Special types
Boolean
A Boolean data type can hold one of three possible values: true, false or NULL. You use boolean
or bool
keyword to declare a column with the Boolean data type.
When you insert data into a Boolean column, PostgreSQL will convert it into the Boolean value e.g., 1, yes, y, t, true are converted to true, and 0, no, n false, f are converted to false.
When you select data from a Boolean column, PostgreSQL displays t
for true, f
for false and space character for NULL.
Character data types
PostgreSQL provides three character data types: char(n), varchar(n), and text
-
char(n)
is the fixed-length character with blank padded. If you insert a string that is shorter than the length of the column, PostgreSQL will pad spaces. If you insert a string that is longer than the length of the column, PostgreSQL will issue an error. -
varchar(n)
is the variable-length character string. You can store up ton
characters with variable-length character strings. PostgreSQL does not pad spaces when the stored string is shorter than the length of the column. -
text
is variable-length character strings that you do not need to specify the length in the type declaration. Theoretically, a text is an unlimited length character string.
Number data types
PostgreSQL provides two distinct types of numbers:
- integers
- floating-point numbers
Integer
There are three kinds of integers:
- Small integer (
smallint
) is 2-byte signed integer that has a range of (-32768,32767) - Integer (
int
) is 4-byte integer that has a range of (-214783648, -214783647) - Serial is the same as integer except that PostgreSQL will generate and populate values into the column automatically. This is similar to
AUTO_INCREMENT
attribute in other RDMBS such as MySQL.
Floating-point number
There three main types of floating-point numbers:
float(n)
is a floating-point number whose precision, at least, n, up to a maximum of 8 bytes.real
orfloat8
is a double-precision (8-byte) floating-point number.numeric
ornumeric(p,s)
is a real number with p digits with s number after the decimal point. Thenumeric(p,s)
is the exact number.
Temporal data types
The temporal data types store date and time-related data. There are five main temporal data types in PostgreSQL:
date
stores date values only.time
stores time of day values.timestamp
stores date and time.interval
stores periods of time.timestamptz
store both timestamp and time zone data. Thetimestamptz
is a PostgreSQL’s extension to the temporal data type.
Special data types
Besides the primitive data types, PostgreSQL also provides several special data types related to geometric and network.
box
– a rectangular box.line
– a set of points.point
– a geometric pair of numbers.lseg
– a line segment.polygon
– a closed geometric.inet
– an IP4 address.macaddr
– aMAC
address.
Arrays
In PostgreSQL, you can store an array of strings, an array of integers, etc., in an array column of a table. The array comes to handy in some situations e.g., storing days of the week, months of the year, etc.
In this tutorial, we have introduced you to the PostgreSQL data types so that you can use them to create tables in the next tutorial.