Postgres has the ability to define SERIAL datatypes and SEQUENCE but they seem to overlap in function. What are the main differences ?
Asked
Active
Viewed 598 times
-3
-
See dezso's in this q – ypercubeᵀᴹ Apr 13 '15 at 21:35
-
1The link to the manual that you posted explains that in detail – Apr 13 '15 at 22:07
1 Answers
6
serial is purely a shorthand way to create an integer column with an associated sequence for its default values.
The documentation for serial linked from the question even says as much:
The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:
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;
Colin 't Hart
- 9,323
- 15
- 35
- 43
-
That's very interesting, because there is no mention of a "unique" constraint in there. That seems to imply that using the "serial" type (notational shorthand, not really type) does not guarantee uniqueness—an important point. – Wildcard Dec 22 '17 at 02:21