Creating a PostgreSQL SERIAL column using pgAdmin3

When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?

Should we keep this type defined by pgAdmin? Manually create the table with SQL defining id column as type serial causes the pgAdmin SQL pane to again display it as nextval('prices_id_seq'::regclass).

pgAdmin SQL Pane

id integer NOT NULL DEFAULT nextval('prices_id_seq'::regclass)

This is not a pgAdmin feature but that of PostgreSQL. If you check your table from an other client (eg. psql) you will see the exact same column definition. Why is it so?


The data types 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;

It is always useful to consult the documentation.

StackExchange Database Administrators Q#40856, answer score: 4