PostgreSQL: serial vs integer sequences?

[Nick Renders]

Thanks for all the feedback, guys.

I think I got the gist of it now, but there is one thing that is still 
confusing:
if I use pgAdmin to look at a table definition, my old v9.4 tables show 
"serial",
but the new v9.6 tables show "integer". Even new tables that I create 
with:

	CREATE TABLE tablename (field1 serial NOT NULL, field2 integer, ... )

show up as:

	CREATE TABLE tablename (field1 integer NOT NULL DEFAULT 
nextval('tablename_field1_seq'::regclass), ...)


However, when I use the psql command in Terminal, all tables in both 
v9.4 and v9.6
show something like:

\d tablename
                                            Table "public.tablename"
         Column        |           Type           |                      
        Modifiers
----------------------+--------------------------+-------------------------------------------------------------------
  field1               | integer                  | not null default 
nextval('tablename_field1_seq'::regclass)



So I am guessing it is just pgAdmin that parses the table definition on 
the fly
and shows "serial" where applicable. But I don't understand why the same 
pgAdmin
does this for my v9.4 tables and not the v9.6 ones?


Nick

PS: Lou, you can now give me grief for using pgAdmin in the first place 
;-)



On 6 Mar 2017, at 11:31, Nick Renders wrote:

> Hi Everybody,
>
> We recently upgraded our Postgres server from v9.4 to v9.6 and 
> exported/imported
> all our data in doing so. I noticed that all the columns of type 
> serial have been
> converted to integer. The sequences are still in place, so I didn't 
> think too much
> of it, but now that I need to create some new tables, I am wondering:
>
> Is there any reason NOT to use the serial type for sequence fields?
>
> Using integer fields for sequences seems so much clunkier.
>
> Cheers,
>
> Nick Renders
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com