|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.1.4 Serial Types
The data types
are not true types, but merely
a notational convenience for setting up unique identifier columns
(similar to the
supported by some other databases). In the current
CREATE TABLE tablename ( colname SERIAL );
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
Thus, we have created an integer column and arranged for its default
values to be assigned from a sequence generator. A
constraint is applied to ensure that a null value cannot be explicitly
inserted, either. (In most cases you would also want to attach a
PRIMARY KEY constraint to prevent
duplicate values from being inserted by accident, but this is
not automatic.) Lastly, the sequence is marked as “owned by”
the column, so that it will be dropped if the column or table is dropped.
Note: Prior to PostgreSQL 7.3,
UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type.
To insert the next value of the sequence into the
column, specify that the
column should be assigned its default value. This can be done
either by excluding the column from the list of columns in
INSERT statement, or through the use of
DEFAULT key word.
The type names
equivalent: both create
integer columns. The type
serial8 work just
the same way, except that they create a
bigserial should be used if you anticipate
the use of more than 231 identifiers over the
lifetime of the table.
The sequence created for a
serial column is
automatically dropped when the owning column is dropped.
You can drop the sequence without dropping the column, but this
will force removal of the column default expression.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|