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
