Data Types - PostgreSQL: Up and Running - page 80

Serials
serial and its bigger sibling, bigserial , are autoincrementing integers often used as
primary keys of tables in which a natural key is not apparent. This data type goes by
different names in different database products, with autonumber being the most com‐
mon alternative moniker. When you create a table and specify a column as serial ,
PostgreSQL first creates an integer column and then creates a sequence object named
table_name _ column_name _seq located in the same schema as the table. It then sets the
default of the new integer column to read its value from the sequence. If you delete the
column, PostgreSQL also deletes the companion sequence object.
In PostgreSQL, the sequence type is a database asset in its own right. You can inspect
and edit the sequence using pgAdmin or SQL with the ALTER SEQUENCE command. You
can set its current value, boundary values (the upper and lower bounds), and even how
many numbers to increment each time. Though it is rare to increment downward, you
can set the increment value to a negative number to achieve that. Because sequences
are independent database assets, you can create them separately from a table using the
CREATE SEQUENCE command, and you can use the same sequence object for more than
one table. The cross-table sharing of the same sequence comes in handy when you're
assigning a “universal” key in your database.
In order to use the same sequence for multiple tables, define the column as integer or
bigint , then set the default value of the column to the next sequence number using
nextval( sequence_name ) function.
If you rename a table that has a serial based on a sequence, Post‐
greSQL will not automatically rename the sequence object. If main‐
taining naming symmetry is important, you should rename the se‐
quence object.
Generate Series Function
PostgreSQL has a nifty function called generate_series that we have yet to find in
other database products. What makes generate_series so convenient is that it allows
you to effectively mimic a for loop in SQL. Suppose we want a list of the last day of each
month for a particular date range. Doing this without generate_series would involve
either a procedural loop or creating a massive Cartesian product of dates and then
filtering them. With generate_series , your code is a one-liner, as shown later in
Example 5-11 .
Example 5-1 uses integers with an optional step parameter.
Example 5-1. generate_series() with stepping of 13
SELECT x FROM generate_series ( 1 , 51 , 13 ) As x ;