PostgreSQL: Up and Running, 3rd Edition

Chapter 5. Data Types

PostgreSQL supports the workhorse data types of any database: numerics, strings, dates, times, and booleans. But PostgreSQL sprints ahead by adding support for arrays, time zone−aware datetimes, time intervals, ranges, JSON, XML, and many more. 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 showcase data types that are unique to PostgreSQL and nuances in how PostgreSQL handles common data types.

No data type would be useful without a cast of supporting functions and operators. And PostgreSQL has plenty of them. We’ll cover 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 either unary (having one argument) or binary (having two arguments) such as +, -, *, or /. 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 data types and a nifty function to quickly generate arithmetic series of integers.

Serials

Serial and its bigger sibling, bigserial, are auto-incrementing integers often used as primary keys of tables in ...