Creating a PostgreSQL SERIAL column using pgAdmin3

Question

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)

Answer 1

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.

Answer 2

deszo is right, of course, but it's not the whole story.

pgAdmin actually reverse engineers the code, and if all criteria for a serial column are met, a serial column is displayed as such.

CREATE TABLE foo
(
  foo_id serial ...

There was a bug in some old version that caused pgAdmin to fail in this respect, but it has long since been fixed. You fail to supply your version of pgAdmin, but I would suggest to try it with the current version. That's 1.16.1 at the time of writing.
http://www.pgadmin.org/