Re: serial

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

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