Re: serial

On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <grosso(at)sadaic(dot)org(dot)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.