Re: CREATE TABLE LIKE and SERIAL

2009/10/30 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Thom Brown <thombrown(at)gmail(dot)com> writes:
>> Well I realise SERIAL is a convenience rather than a datatype in its
>> own right, but I'm surprised that LIKE can't differentiate between a
>> column created with integer and one created with serial.  The table
>> continues to report a serial datatype after its creation.
>
> Really?
>
> regression=# create table foo (f1 serial);
> NOTICE:  CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
> CREATE TABLE
> regression=# \d foo
>                         Table "public.foo"
>  Column |  Type   |                    Modifiers
> --------+---------+--------------------------------------------------
>  f1     | integer | not null default nextval('foo_f1_seq'::regclass)
>
> regression=#
>
> We used to try to treat serial as more like a real type (in particular
> pg_dump used to try to dump the results of this using "serial") but we
> found out that that was actively a bad idea, because there were too
> many corner cases where it did the wrong thing.  I doubt we'll want
> to go back in that direction.
>

Erk... that's strange. I've obviously made the wrong assumptions
here. I'm basing it on pgAdmin describing the table as it reports a
SERIAL datatype. Not sure how it's deciding that. I created a manual
sequence, then a new table with an integer column that gets it's
default value from the sequence, set that to be NOT NULL and act as
the primary key, but it still reports it as an integer, so it doesn't
seem to be assuming it based on these constraints.

How is pgAdmin determining the serial type in this case?

Thom