4

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)
1
  • This seems more like a feature request for the product in question rather than an actual question. @CraigRinger did a great job of explaining the ::regclass thing here. I suspect that pgAdmin3 is simply being overly verbose.
    – swasheck
    Apr 25, 2013 at 4:08

2 Answers 2

4

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.

1
1

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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