Re: serial

On Thu, Dec 4, 2008 at 7:06 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> 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.
>

Last way, forgot about it:


smarlowe=# select nextval('test_i_seq');
 nextval
---------
       5
smarlowe=# insert into test (i,t) values (currval('test_i_seq'),'last
bit of text');
INSERT 0 1


--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis