Thread: SERIAL type feature request

SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Hi!

I would like to add an entry to PostgreSQL 8.2 TODO:
- Extend SERIAL to a full-featured auto-incrementer type.

To achieve this, the following three requirements should be fulfilled:

1. The statement parser should be able to handle this:

create table x (   id serial(N),   ...
);

and behind the scenes this would translate into the "create sequence ... 
start N"
before creating the table.

2. Upon INSERTing to a serial column, explicitly given 0 value or 
'default' keyword
or omitted field (implicit default) should be interchangeable.

3. When a serial field value is given in an INSERT or UPDATE statement
and the value is larger the the current value of the sequence then the 
sequence
should be modified accordingly.

This is the way Informix handles its serial type, although it doesn't seem
to have a visible sequence bound to the serial column.

Is it feasible in the 8.2 timeframe?

Thanks in advance,
Zoltán Böszörményi



Re: SERIAL type feature request

From
Tino Wildenhain
Date:
Am Samstag, den 03.12.2005, 22:23 +0100 schrieb Zoltan Boszormenyi:
> Hi!
> 
> I would like to add an entry to PostgreSQL 8.2 TODO:
> - Extend SERIAL to a full-featured auto-incrementer type.
> 
> To achieve this, the following three requirements should be fulfilled:
> 
> 1. The statement parser should be able to handle this:
> 
> create table x (
>     id serial(N),
>     ...
> );
> 
> and behind the scenes this would translate into the "create sequence ... 
> start N"
> before creating the table.

why isnt N max_id? Or increment?
Sounds inconsistent. -1 on this.

> 2. Upon INSERTing to a serial column, explicitly given 0 value or 
> 'default' keyword
> or omitted field (implicit default) should be interchangeable.

default and omit are these. 0 would be an error. -1 on this too.

> 3. When a serial field value is given in an INSERT or UPDATE statement
> and the value is larger the the current value of the sequence then the 
> sequence
> should be modified accordingly.

sideeffects, raceconditions. -1 on this.

> This is the way Informix handles its serial type, although it doesn't seem
> to have a visible sequence bound to the serial column.

Sounds like this informix is seriously broken ;)


> Is it feasible in the 8.2 timeframe?

I hope not ;)





Re: SERIAL type feature request

From
Andrew Dunstan
Date:

Zoltan Boszormenyi wrote:

> Hi!
>
> I would like to add an entry to PostgreSQL 8.2 TODO:
> - Extend SERIAL to a full-featured auto-incrementer type.
>
>
I can't see this item on the TODO list at all. Where exactly did you 
find it?

cheers

andrew


Re: SERIAL type feature request

From
Josh Berkus
Date:
Zoltan,

> > I would like to add an entry to PostgreSQL 8.2 TODO:
> > - Extend SERIAL to a full-featured auto-incrementer type.

I believe that our SERIAL/SEQUENCE stuff is already in compliance with the 
SQL standard for sequences (in SQL03).   Why would we change it?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco


Re: SERIAL type feature request

From
Peter Eisentraut
Date:
Josh Berkus wrote:
> I believe that our SERIAL/SEQUENCE stuff is already in compliance
> with the SQL standard for sequences (in SQL03).   Why would we change
> it?

Because your belief is wrong, but Zoltan's proposal is not getting is 
closer.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: SERIAL type feature request

From
Jan Wieck
Date:
On 12/3/2005 4:23 PM, Zoltan Boszormenyi wrote:

> Hi!
> 
> I would like to add an entry to PostgreSQL 8.2 TODO:
> - Extend SERIAL to a full-featured auto-incrementer type.
> 
> To achieve this, the following three requirements should be fulfilled:
> 
> 1. The statement parser should be able to handle this:
> 
> create table x (
>     id serial(N),
>     ...
> );
> 
> and behind the scenes this would translate into the "create sequence ... 
> start N"
> before creating the table.

Syntactic sugar with zero real value. A setval() after create table does 
exactly the same. Unless you extend your proposal to unambiguosly 
specify any or all of the serials properties (min, max, start, cache, 
cycle), this has to be rejected as incomplete.

> 
> 2. Upon INSERTing to a serial column, explicitly given 0 value or 
> 'default' keyword
> or omitted field (implicit default) should be interchangeable.

Why exactly would we treat an explicit zero different from any other 
explicit value? What you are asking for is to substitute an explicit, 
literal value presented by the user with something different. Sorry, but 
if Informix does THAT, then Informix is no better than MySQL.

> 
> 3. When a serial field value is given in an INSERT or UPDATE statement
> and the value is larger the the current value of the sequence then the 
> sequence
> should be modified accordingly.

How about negative increment values, cycling sequences and max/minval?

> 
> This is the way Informix handles its serial type, although it doesn't seem
> to have a visible sequence bound to the serial column.

Have you considered asking Informix to do the reverse changes?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Andrew Dunstan írta:

>
>
> Zoltan Boszormenyi wrote:
>
>> Hi!
>>
>> I would like to add an entry to PostgreSQL 8.2 TODO:
>> - Extend SERIAL to a full-featured auto-incrementer type.
>>
>>
> I can't see this item on the TODO list at all. Where exactly did you 
> find it?


That's why I wanted it ADDed... ;-)

Best regards



Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Peter Eisentraut írta:

>Josh Berkus wrote:
>  
>
>>I believe that our SERIAL/SEQUENCE stuff is already in compliance
>>with the SQL standard for sequences (in SQL03).   Why would we change
>>it?
>>    
>>
>
>Because your belief is wrong, but Zoltan's proposal is not getting is 
>closer.
>
>  
>
OK, what does the standard say on SERIAL for specifying the start value?
And about this:

<last serial value was e.g. 307>
insert into mytable (serial_id, ...) values (500, ...);
delete from mytable where serial_id = 500;

In Informix, this is a way to start the next serial value at 501.

Best regards,
Zoltán



Re: SERIAL type feature request

From
Bruce Momjian
Date:
Zoltan Boszormenyi wrote:
> Peter Eisentraut ?rta:
> 
> >Josh Berkus wrote:
> >  
> >
> >>I believe that our SERIAL/SEQUENCE stuff is already in compliance
> >>with the SQL standard for sequences (in SQL03).   Why would we change
> >>it?
> >>    
> >>
> >
> >Because your belief is wrong, but Zoltan's proposal is not getting is 
> >closer.
> >
> >  
> >
> OK, what does the standard say on SERIAL for specifying the start value?
> And about this:
> 
> <last serial value was e.g. 307>
> insert into mytable (serial_id, ...) values (500, ...);
> delete from mytable where serial_id = 500;
> 
> In Informix, this is a way to start the next serial value at 501.

This seems much stranger than a simple setval(), which get got from
Oracle.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: SERIAL type feature request

From
Andrew Dunstan
Date:

Zoltan Boszormenyi wrote:

> Andrew Dunstan írta:
>
>>
>>
>> Zoltan Boszormenyi wrote:
>>
>>> Hi!
>>>
>>> I would like to add an entry to PostgreSQL 8.2 TODO:
>>> - Extend SERIAL to a full-featured auto-incrementer type.
>>>
>>>
>> I can't see this item on the TODO list at all. Where exactly did you 
>> find it?
>
>
>
> That's why I wanted it ADDed... ;-)
>
>

I misread. I apologise.

However, I think it's up to you to demonstrate why the Informix way of 
doing things is better than what we have, on a matter on which (AFAICS) 
the standard is silent.

What you propose would be backwards incompatible, which we try to avoid.


cheers

andrew


Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
OK, I admit I haven't read the SQL standards on this matter.

Tino Wildenhain írta:

>Am Samstag, den 03.12.2005, 22:23 +0100 schrieb Zoltan Boszormenyi:
>  
>
>>Hi!
>>
>>I would like to add an entry to PostgreSQL 8.2 TODO:
>>- Extend SERIAL to a full-featured auto-incrementer type.
>>
>>To achieve this, the following three requirements should be fulfilled:
>>
>>1. The statement parser should be able to handle this:
>>
>>create table x (
>>    id serial(N),
>>    ...
>>);
>>
>>and behind the scenes this would translate into the "create sequence ... 
>>start N"
>>before creating the table.
>>    
>>
>
>why isnt N max_id? Or increment?
>Sounds inconsistent. -1 on this.
>  
>

A SERIAL type has the assumption that its value starts at a low value 
(1) and
is increasing. Or is there a type modifier keyword that makes it work 
backwards?
A start value would also work here, decreasing from there.

>  
>
>>2. Upon INSERTing to a serial column, explicitly given 0 value or 
>>'default' keyword
>>or omitted field (implicit default) should be interchangeable.
>>    
>>
>
>default and omit are these. 0 would be an error. -1 on this too.
>  
>
Why? A sequence in PostgreSQL won't give you 0 even in wraparound mode.
I just checked it:

db=> create sequence proba_seq maxvalue 5 cycle;
CREATE SEQUENCE
db=> select nextval('proba_seq');nextval
---------      1
(1 sor)

db=> select nextval('proba_seq');nextval
---------      2
(1 sor)

db=> select nextval('proba_seq');nextval
---------      3
(1 sor)

db=> select nextval('proba_seq');nextval
---------      4
(1 sor)

db=> select nextval('proba_seq');nextval
---------      5
(1 sor)

db=> select nextval('proba_seq');nextval
---------      1
(1 sor)


>>3. When a serial field value is given in an INSERT or UPDATE statement
>>and the value is larger the the current value of the sequence then the 
>>sequence
>>should be modified accordingly.
>>    
>>
>
>sideeffects, raceconditions. -1 on this.
>  
>
This event doesn't (shouldn't) occur often, e.g. you have an invoice 
table, invoice No.
contains the year, too. It's somewhat natural to handle it with the 
serial field, so
it gives out 200500001 ... values. At the beginning of the next year, 
you modify
the sequence to start at 200600001. What I mean is that there may be two 
paths
in the serial field handling, one where 'default' is used, it's 
hopefully isn't racy
as this is the way it works now. The other is when the value is 
explicitly given,
a little critical section may not hurt:

Lock sequence
Check the current value of section
If given value is higher Then Modify sequence
Unlock sequence

>>This is the way Informix handles its serial type, although it doesn't seem
>>to have a visible sequence bound to the serial column.
>>    
>>
>
>Sounds like this informix is seriously broken ;)
>  
>

Yes, and slow, too. :-( That's why I would like to port the company's 
software to PostgreSQL
but there way too many places where "Informixism" were used.

>>Is it feasible in the 8.2 timeframe?
>>    
>>
>
>I hope not ;)
>  
>



Re: SERIAL type feature request

From
Tino Wildenhain
Date:
Am Sonntag, den 04.12.2005, 08:52 +0100 schrieb Zoltan Boszormenyi:
> OK, I admit I haven't read the SQL standards on this matter.
> 
> Tino Wildenhain írta:
> 
...
> A SERIAL type has the assumption that its value starts at a low value 
> (1) and
> is increasing. Or is there a type modifier keyword that makes it work 
> backwards?
> A start value would also work here, decreasing from there.

There is no serial type ;) serial is only a macro which boils down
to int4/int8 and a default value of nextval('some_sequence')

This is a little bit kludgy, but I dont know how much you would
gain from a true type.

> >  
> >
> >>2. Upon INSERTing to a serial column, explicitly given 0 value or 
> >>'default' keyword
> >>or omitted field (implicit default) should be interchangeable.
> >
> >default and omit are these. 0 would be an error. -1 on this too.
> >  
> >
> Why? A sequence in PostgreSQL won't give you 0 even in wraparound mode.
> I just checked it:

This does not mean we should magically translate values to something
other. We arent MySQL. We are ACID.

> >>3. When a serial field value is given in an INSERT or UPDATE statement
> >>and the value is larger the the current value of the sequence then the 
> >>sequence
> >>should be modified accordingly.
> >
> >sideeffects, raceconditions. -1 on this.
> >  
> >
> This event doesn't (shouldn't) occur often, e.g. you have an invoice 
> table, invoice No.
> contains the year, too. It's somewhat natural to handle it with the 
> serial field, so
> it gives out 200500001 ... values. At the beginning of the next year, 
> you modify
> the sequence to start at 200600001. What I mean is that there may be two 
> paths

Well, you can use setval() for this. Why would you want to do this
inbound? The whole point of sequences is not to set a value
explicitely. Who is the first who set it? And why and when
should it fail?

After all, if you want a year in the number, use a year.
e.g. prepend your serials with to_char(now(),'YYYY')

...
> >Sounds like this informix is seriously broken ;)
> >  
> >
> 
> Yes, and slow, too. :-( That's why I would like to port the company's 
> software to PostgreSQL
> but there way too many places where "Informixism" were used.

Maybe you can translate these Informixisms to the way postgres
works. It is always some work to migrate from one db to another.
Its quite popular with MySQL->postgres, but I think you should
get by with Informix as well. There arent just so many howtows
on that matter by now.

If you have special issues you need to solve, just ask on the
list for ideas. But I really doubt there is really a point
to modify postgres to the way a slow and sucky database works .-)

++Tino



Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Jan Wieck írta:

> On 12/3/2005 4:23 PM, Zoltan Boszormenyi wrote:
>
>> Hi!
>>
>> I would like to add an entry to PostgreSQL 8.2 TODO:
>> - Extend SERIAL to a full-featured auto-incrementer type.
>>
>> To achieve this, the following three requirements should be fulfilled:
>>
>> 1. The statement parser should be able to handle this:
>>
>> create table x (
>>     id serial(N),
>>     ...
>> );
>>
>> and behind the scenes this would translate into the "create sequence 
>> ... start N"
>> before creating the table.
>
>
> Syntactic sugar with zero real value. A setval() after create table 
> does exactly the same. Unless you extend your proposal to unambiguosly 
> specify any or all of the serials properties (min, max, start, cache, 
> cycle), this has to be rejected as incomplete.


I found this in the SQL2003 draft:

"
4.14.7 Identity columns

... An identity column has a start value, an increment, a maximum value, 
a minimum value,
and a cycle option. ...
"

The exact properties of a sequence. It would be a good idea to be able 
to provide
all these the same way PostgreSQL provides CREATE SEQUENCE.

>> 2. Upon INSERTing to a serial column, explicitly given 0 value or 
>> 'default' keyword
>> or omitted field (implicit default) should be interchangeable.
>
>
>
> Why exactly would we treat an explicit zero different from any other 
> explicit value? What you are asking for is to substitute an explicit, 
> literal value presented by the user with something different. Sorry, 
> but if Informix does THAT, then Informix is no better than MySQL.


Thinking about it more, 0 is a special value that a sequence created 
with defaults
(just like the ones created for SERIAL fields) will not produce. If 
PostgreSQL
provides a way to specify the sequence parameters for a SERIAL, there 
may be other
values too, that a sequence created with given parameters will not produce.
At the extreme, they may be handled the same way. E.g.
CREATE SEQUENCE seq1 INCREMENT 2 MINVALUE 2 MAXVALUE 100;
won't produce 0, 1, any odd number between 3 and 99, and numbers 101 ... 
2^64 -1.

>> 3. When a serial field value is given in an INSERT or UPDATE statement
>> and the value is larger the the current value of the sequence then 
>> the sequence
>> should be modified accordingly.
>
>
>
> How about negative increment values, cycling sequences and max/minval?


For descending sequences, a lower value should update the sequence.

>> This is the way Informix handles its serial type, although it doesn't 
>> seem
>> to have a visible sequence bound to the serial column.
>
>
>
> Have you considered asking Informix to do the reverse changes?


Hm. Good idea. I'll try. But I guess they won't backport it to 9.21. :-)

Best regards,
Zoltán Böszörményi



Re: SERIAL type feature request

From
Andrew Dunstan
Date:

Zoltan Boszormenyi wrote:

>
>
> I found this in the SQL2003 draft:
>
> "
> 4.14.7 Identity columns
>
> ... An identity column has a start value, an increment, a maximum 
> value, a minimum value,
> and a cycle option. ...



And that section says nothing at all about using 0 as a magic value. All 
it says is:

"Let IC be the identity column of BT. When a row R is presented for 
insertion
into BT, if R does not contain a column corresponding to IC, then the 
value V for IC in the row inserted into
BT is obtained by applying the General Rules of Subclause 9.21, 
“Generation of the next value of a sequence
generator”, to SG."

Which is pretty much what we do.

We can't implement everybody's way of doing serial cols, because they 
are simply not all compatible. Next someone will be asking us to do it 
MySQL's way (gods forbid).

cheers

andrew


Re: SERIAL type feature request

From
Jan Wieck
Date:
On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
> I found this in the SQL2003 draft:
> 
> "
> 4.14.7 Identity columns
> 
> ... An identity column has a start value, an increment, a maximum value, 
> a minimum value,
> and a cycle option. ...
> "
> 
> The exact properties of a sequence. It would be a good idea to be able 
> to provide
> all these the same way PostgreSQL provides CREATE SEQUENCE.

I think nobody would object to implementing support for the SQL2003 
syntax. Most of that would be providing all the values that will get 
forwarded into the internal sequence generation during CREATE TABLE.

The other thing needed is an extension to the default value mechanism 
that overrides any given value to implement GENERATE ALLWAYS. Not too 
hard either.

> Thinking about it more, 0 is a special value that a sequence created 
> with defaults
> (just like the ones created for SERIAL fields) will not produce. If 
> PostgreSQL

Zero is no more special than any other value and the standard you 
pointed to does not talk about it either. If we implement IDENTITY as 
per standard, you will either omit the value, specify DEFAULT or declare 
the column GENERATE ALLWAYS.

If we had to pick any magic value I would vote for skipping 666 in all 
sequence generators and use that.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: SERIAL type feature request

From
Michael Glaesemann
Date:
On Dec 5, 2005, at 9:50 , Jan Wieck wrote:

> If we had to pick any magic value I would vote for skipping 666 in  
> all sequence generators and use that.

What about 13?

Michael Glaesemann
grzm myrealbox com





Re: SERIAL type feature request

From
Christopher Kings-Lynne
Date:
> I think nobody would object to implementing support for the SQL2003 
> syntax. Most of that would be providing all the values that will get 
> forwarded into the internal sequence generation during CREATE TABLE.

Someone also pointed out on IRC the other day that Oracle and DB2 list 
'identity' as the column type of identity columns in the 
information_schema, whereas we just put 'integer'.  Maybe we could 
change that to match in the future...

Chris



Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Jan Wieck írta:

> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
>
>> I found this in the SQL2003 draft:
>>
>> "
>> 4.14.7 Identity columns
>>
>> ... An identity column has a start value, an increment, a maximum 
>> value, a minimum value,
>> and a cycle option. ...
>> "
>>
>> The exact properties of a sequence. It would be a good idea to be 
>> able to provide
>> all these the same way PostgreSQL provides CREATE SEQUENCE.
>
>
> I think nobody would object to implementing support for the SQL2003 
> syntax. Most of that would be providing all the values that will get 
> forwarded into the internal sequence generation during CREATE TABLE.
>
> The other thing needed is an extension to the default value mechanism 
> that overrides any given value to implement GENERATE ALLWAYS. Not too 
> hard either.


Where can I find this syntax? (PDF file name, page#) Thanks.
I think I modify my feature request for the standard behaviour.

Thanks and best regards,
Zoltán Böszörményi



Re: SERIAL type feature request

From
Jan Wieck
Date:
On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote:
> Jan Wieck írta:
> 
>> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
>>
>>> I found this in the SQL2003 draft:
>>>
>>> "
>>> 4.14.7 Identity columns
>>>
>>> ... An identity column has a start value, an increment, a maximum 
>>> value, a minimum value,
>>> and a cycle option. ...
>>> "
>>>
>>> The exact properties of a sequence. It would be a good idea to be 
>>> able to provide
>>> all these the same way PostgreSQL provides CREATE SEQUENCE.
>>
>>
>> I think nobody would object to implementing support for the SQL2003 
>> syntax. Most of that would be providing all the values that will get 
>> forwarded into the internal sequence generation during CREATE TABLE.
>>
>> The other thing needed is an extension to the default value mechanism 
>> that overrides any given value to implement GENERATE ALLWAYS. Not too 
>> hard either.
> 
> 
> Where can I find this syntax? (PDF file name, page#) Thanks.
> I think I modify my feature request for the standard behaviour.

It's all in the Foundation paper inside this zip:

http://www.wiscorp.com/sql/sql_2003_standard.zip


Jan


> 
> Thanks and best regards,
> Zoltán Böszörményi
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Jan Wieck írta:

> On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote:
>
>> Jan Wieck írta:
>>
>>> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
>>>
>>>> I found this in the SQL2003 draft:
>>>>
>>>> "
>>>> 4.14.7 Identity columns
>>>>
>>>> ... An identity column has a start value, an increment, a maximum 
>>>> value, a minimum value,
>>>> and a cycle option. ...
>>>> "
>>>>
>>>> The exact properties of a sequence. It would be a good idea to be 
>>>> able to provide
>>>> all these the same way PostgreSQL provides CREATE SEQUENCE.
>>>
>>>
>>>
>>> I think nobody would object to implementing support for the SQL2003 
>>> syntax. Most of that would be providing all the values that will get 
>>> forwarded into the internal sequence generation during CREATE TABLE.
>>>
>>> The other thing needed is an extension to the default value 
>>> mechanism that overrides any given value to implement GENERATE 
>>> ALLWAYS. Not too hard either.
>>
>>
>>
>> Where can I find this syntax? (PDF file name, page#) Thanks.
>> I think I modify my feature request for the standard behaviour.
>
>
> It's all in the Foundation paper inside this zip:
>
> http://www.wiscorp.com/sql/sql_2003_standard.zip


Thanks, I found it. It's "GENERATED { ALWAYS | BY DEFAULT  } AS 
IDENTITY, isn't it?
If I interpret it correctly, "GENERATED ALWAYS AS IDENTITY" means that 
no matter
what I give in "INSERT INTO MYTABLE (serial_id, ...) VALUES (N, ...)", 
the sequence
next value will be inserted into the database. I am all for it, it's 
much stronger than just watching
for the 0 value and would fit my needs.

The other behaviour is "GENERATED  BY DEFAULT AS IDENTITY",
which is what PostgreSQL currently provides.

Best regards,
Zoltán Böszörményi



Re: SERIAL type feature request

From
Zoltan Boszormenyi
Date:
Hi,

Zoltan Boszormenyi írta:

> Jan Wieck írta:
>
>> On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote:
>>
>>> Jan Wieck írta:
>>>
>>>> On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
>>>>
>>>>> I found this in the SQL2003 draft:
>>>>>
>>>>> "
>>>>> 4.14.7 Identity columns
>>>>>
>>>>> ... An identity column has a start value, an increment, a maximum 
>>>>> value, a minimum value,
>>>>> and a cycle option. ...
>>>>> "
>>>>>
>>>>> The exact properties of a sequence. It would be a good idea to be 
>>>>> able to provide
>>>>> all these the same way PostgreSQL provides CREATE SEQUENCE.
>>>>
>>>>
>>>>
>>>>
>>>> I think nobody would object to implementing support for the SQL2003 
>>>> syntax. Most of that would be providing all the values that will 
>>>> get forwarded into the internal sequence generation during CREATE 
>>>> TABLE.
>>>>
>>>> The other thing needed is an extension to the default value 
>>>> mechanism that overrides any given value to implement GENERATE 
>>>> ALLWAYS. Not too hard either.
>>>
>>>
>>>
>>>
>>> Where can I find this syntax? (PDF file name, page#) Thanks.
>>> I think I modify my feature request for the standard behaviour.
>>
>>
>>
>> It's all in the Foundation paper inside this zip:
>>
>> http://www.wiscorp.com/sql/sql_2003_standard.zip
>
>
>
> Thanks, I found it. It's "GENERATED { ALWAYS | BY DEFAULT  } AS 
> IDENTITY, isn't it?
> If I interpret it correctly, "GENERATED ALWAYS AS IDENTITY" means that 
> no matter
> what I give in "INSERT INTO MYTABLE (serial_id, ...) VALUES (N, ...)", 
> the sequence
> next value will be inserted into the database. I am all for it, it's 
> much stronger than just watching
> for the 0 value and would fit my needs.
>
> The other behaviour is "GENERATED  BY DEFAULT AS IDENTITY",
> which is what PostgreSQL currently provides.
>
> Best regards,
> Zoltán Böszörményi


To reiterate it, I would like the following added to PostgreSQL 8.2 TODO 
(I may have got the optional parametes wrong...):

- Extend SERIAL type declaration and functionality with the SQL2003 
compliant sequence generation options:   SERIAL [ GENERATED { ALWAYS | BY DEFAULT }            [ AS IDENTITY ( [ START
WITHstartvalue ] [ INCREMENT BY 
 
incrementvalue ]                                           [ MAXVALUE maxvalue ] [ 
MINVALUE minvalue ] [ CYCLE | NO CYCLE ] ) ] ]