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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
