Thread: SERIAL data type
A few questions for new SERIAL data type:
o dropping a table including a serial data type does not drop the sequence
corresponding to the table. This prevents re-creating the table.
Do we have to remove the sequence by hand?
o explicit insertion to the serial column sets the value specified.
This is good. However, next implicit insertion results in that
previous value from the sequence + 1 is set.
create table t (c text, i serial);
insert into t values('a');
insert into t values('b',100);
insert into tvalues('c');
select * from t;
a 1
b 100
c 2
This seems a little bit unnatural for me. may be i of the thrid row
should be 101?
--
Tatsuo Ishii
t-ishii@sra.co.jp
Thus spake Tatsuo Ishii
> o explicit insertion to the serial column sets the value specified.
> This is good. However, next implicit insertion results in that
> previous value from the sequence + 1 is set.
>
> create table t (c text, i serial);
> insert into t values('a');
> insert into t values('b',100);
> insert into tvalues('c');
> select * from t;
>
> a 1
> b 100
> c 2
>
> This seems a little bit unnatural for me. may be i of the thrid row
> should be 101?
Perhaps it should not be possible to specify a value for serial types.
Make it always use the next value no matter what is supplied. Could
make dump and restore a problem of course but I can think of some
situations where it would make the programming simpler.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
t-ishii@sra.co.jp (Tatsuo Ishii) writes:
> A few questions for new SERIAL data type:
> [...]
> o explicit insertion to the serial column sets the value specified.
> This is good. However, next implicit insertion results in that
> previous value from the sequence + 1 is set.
>
> create table t (c text, i serial);
> insert into t values('a');
> insert into t values('b',100);
> insert into tvalues('c');
> select * from t;
>
> a 1
> b 100
> c 2
>
> This seems a little bit unnatural for me. may be i of the thrid row
> should be 101?
The action above is definitely 'not right'. It is my opinion that either:
1. Explicit insertion into a serial column be dis-allowed.
2. If explicit insertion is allowed, then the sequence need to be set so
the next implicit insertsion result in the last explicit insertion
value + 1 being used (i.e. 101 instead of 2 in the example above).
Of these two options, I prefer #1.
Also, should we be able to specify a starting value for the sequence when the
table is created? For example "create table t (c text, i serial(1000));" to
set the starting value of the sequence to 1000.
--
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |
> o dropping a table including a serial data type does not drop the
> sequence corresponding to the table. This prevents re-creating the
> table. Do we have to remove the sequence by hand?
Yes, at the moment. I don't have specific plans to change/fix things for
v6.4, but might be possible. It would be OK if someone else wanted to
pick up the coding and carry it forward...
> 1. Explicit insertion into a serial column be dis-allowed.
> 2. If explicit insertion is allowed, then the sequence need to be set
> so the next implicit insertsion result in the last explicit
> insertion value + 1 being used
> Of these two options, I prefer #1.
I wonder if we can use the rewrite rules system to implement #1?
> Also, should we be able to specify a starting value for the sequence
> when the table is created? For example
> create table t (c text, i serial(1000));
> to set the starting value of the sequence to 1000.
There was some discussion of this and at least one person thought it was
an ugly feature (this is available on Sybase, right?). So I didn't even
try to implement it.
- Tom
Hello Billy,
sabato, 12 settembre 98, you wrote:
BGA> t-ishii@sra.co.jp (Tatsuo Ishii) writes:
>> A few questions for new SERIAL data type:
>> [...]
>> o explicit insertion to the serial column sets the value specified.
>> This is good. However, next implicit insertion results in that
>> previous value from the sequence + 1 is set.
>>
>> create table t (c text, i serial);
>> insert into t values('a');
>> insert into t values('b',100);
>> insert into tvalues('c');
>> select * from t;
>>
>> a 1
>> b 100
>> c 2
>>
>> This seems a little bit unnatural for me. may be i of the thrid row
>> should be 101?
BGA> The action above is definitely 'not right'. It is my opinion that either:
BGA> 1. Explicit insertion into a serial column be dis-allowed.
BGA> 2. If explicit insertion is allowed, then the sequence need to be set so
BGA> the next implicit insertsion result in the last explicit insertion
BGA> value + 1 being used (i.e. 101 instead of 2 in the example above).
BGA> Of these two options, I prefer #1.
BGA> Also, should we be able to specify a starting value for the sequence when the
BGA> table is created? For example "create table t (c text, i serial(1000));" to
BGA> set the starting value of the sequence to 1000.
I would like to remember there's another problem with sequences.
If you increment a sequence during a transaction and then the transaction
rolls back, the sequence's original value don't be restored.
Jose'
SS> sabato, 12 settembre 98, you wrote:
BGA>> t-ishii@sra.co.jp (Tatsuo Ishii) writes:
>>> A few questions for new SERIAL data type:
>>> [...]
>>> o explicit insertion to the serial column sets the value specified.
>>> This is good. However, next implicit insertion results in that
>>> previous value from the sequence + 1 is set.
>>>
>>> create table t (c text, i serial);
>>> insert into t values('a');
>>> insert into t values('b',100);
>>> insert into tvalues('c');
>>> select * from t;
>>>
>>> a 1
>>> b 100
>>> c 2
>>>
>>> This seems a little bit unnatural for me. may be i of the thrid row
>>> should be 101?
BGA>> The action above is definitely 'not right'. It is my opinion that either:
BGA>> 1. Explicit insertion into a serial column be dis-allowed.
BGA>> 2. If explicit insertion is allowed, then the sequence need to be set so
BGA>> the next implicit insertsion result in the last explicit insertion
BGA>> value + 1 being used (i.e. 101 instead of 2 in the example above).
BGA>> Of these two options, I prefer #1.
BGA>> Also, should we be able to specify a starting value for the sequence when the
BGA>> table is created? For example "create table t (c text, i serial(1000));" to
BGA>> set the starting value of the sequence to 1000.
I would like to remember there's another problem with sequences.
If you increment a sequence during a transaction and then the transaction
rolls back, the sequence's original value don't be restored.
Jose'