PostgreSQL supports the workhorse data types of any database: numerics, strings, dates and times, and Booleans. But PostgreSQL sprints ahead by adding support for arrays, datetimes with time zones, time intervals, ranges, JSON, XML, and many more types. If that’s not enough, you can invent custom types. In this chapter, we don’t intend to cover every data type. For that, there’s always the manual. We’ll focus on showing you some of the data types that are unique to PostgreSQL and nuances of common data types.
No data type would be useful without the functions and operators used to navigate and work with it. PostgreSQL has an army of functions and operators that cater to each data type, and many extensions add their own. We’ll cover some of the more popular ones in this chapter.
When we use the term function, we’re talking about something that’s of the form
f(. When we use the
term operator, we’re talking about
something that’s symbolic and or unary (having only one argument) or
binary (having two arguments) such as
+, -, *, /. The
simplest operator is a symbol alias for a function that takes one or more
arguments. When using operators, keep in mind that the same symbol can
take on a different meaning when applied to different data types. For
example, the plus sign means adding for numerics but unioning for
You will find your everyday integers, decimals, and floating point numbers in PostgreSQL. Of the numeric types, we want to discuss serial ...