Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
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 '13 at 4:08
add comment

2 Answers

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.

share|improve this answer
    
There's more to it .. –  Erwin Brandstetter Apr 26 '13 at 20:37
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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