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.
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
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
SEQUENCE. You can edit ...