PostgreSQL: Up and Running

Chapter 5. Data Types

PostgreSQL supports the workhorse data types of any database: numerics, characters, dates and times, booleans, and so on. PostgreSQL sprints ahead by adding support for dates and times with time zones, time intervals, arrays and XML. If that’s not enough, you can even add your custom types. In this chapter, we’re not going to dwell on the vanilla data types, but focus more on showing you ones that are unique to PostgreSQL.

Numeric Data Types

You will find your everyday integers, decimals, and floating point numbers in PostgreSQL. Of the numeric types, we just want to highlight the serial and bigserial data types and a nifty function to quickly generate arithmetic series of integers.

Serial

Strictly speaking, serial is not a data type in its own right. Serial and its bigger sibling bigserial are auto-incrementing integers. This data type goes by different names in different databases, autonumber being the most common alternative moniker. When you create a table and specify a column as type serial, PostgreSQL first creates a column of data type integer and then creates a sequence object in the background. It then sets the default of the new integer column to pull its value from the sequence. In PostgreSQL, sequence is a database object in its own right, and an ANSI-SQL standard feature you will also find in Oracle, IBM DB2, SQL Server 2012+, and some other relational databases. You can inspect and edit the object using pgAdmin or with ALTER SEQUENCE. You can edit ...