Re: serial

On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <grosso@sadaic.org.ar> wrote:
> I created this table:
>
> create table se (n  serial);
>
> Column |  Type      |                   Modifiers
> ---------+---------+------------------------------------------------
> n           | integer    | not null default nextval('se_n_seq'::regclass)
>
> I inserted two record, later select, but column n (serial) no
> auto-incremented
> banco=# select * from se;
> n
> ---
> 0
> 0
> (2 rows)
>
> Why?

Because MySQL taught you bad habits?  You told the db to insert a 0,
so it inserted a 0.  If you told it to insert a NULL, it would proceed
to do that too.  There are several ways to have it use the serial /
sequence properly...  You can use the default keyword, or leave out
the field altogether (assuming you have > 1 field I guess), or you can
insert from the sequence yourself:

smarlowe=# create table test (i serial primary key, t text);
NOTICE:  CREATE TABLE will create implicit sequence "test_i_seq" for
serial column "test.i"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# insert into test (t) values ('this is text');
INSERT 0 1
smarlowe=# insert into test (i,t) values (DEFAULT,'this is more text');
INSERT 0 1
smarlowe=# insert into test (i,t) values (nextval('test_i_seq'),'even
more text');
INSERT 0 1
smarlowe=# select nextval('test_i_seq');
 nextval
---------
       4
(1 row)

smarlowe=# insert into test (i,t) values (4,'last bit of text');
INSERT 0 1
smarlowe=# select * from test;
 i |         t
---+-------------------
 1 | this is text
 2 | this is more text
 3 | even more text
 4 | last bit of text
(4 rows)

Hope that helps.