PostgreSQL: Up and Running, 2nd Edition

Chapter 5. Data Types

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.

Tip

When we use the term function, we’re talking about something that’s of the form f(x). 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 ranges.

Numerics

You will find your everyday integers, decimals, and floating point numbers in PostgreSQL. Of the numeric types, we want to discuss serial ...