-3

Postgres has the ability to define SERIAL datatypes and SEQUENCE but they seem to overlap in function. What are the main differences ?

closed as off-topic by Colin 't Hart, dezso, Paul White, RolandoMySQLDBA, Michael Green Apr 14 '15 at 9:55

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, RolandoMySQLDBA, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

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;
  • 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 2:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.