Re: interesting sequence

On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani <johnf(at)jfcomputer(dot)com> wrote:

> On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote:
> > You don't need a loop there. Assuming your order id field is of type
> > varchar you can just build the first part of your string and then do a
> > count to get the last part using a LIKE comparison:
> >
> > select count(id_order) + 1 from sometable WHERE id_order LIKE
> 'O-20110704
> > -%';
> >
> > If you do this inside a function it will be like running it in a
> > transaction so you shouldn't have to worry about it being a multi-user
> > system.
> >
> >
> >
>
> I like this - looks better than what I'm currently doing. Thanks
> Johnf
>
>
It is simpler, but it will result in id collision if two inserts runs at the
same time, particularly if the count query takes a while to run, so be
prepared to handle that. Make sure you have an index which can satisfy that
count query quickly. If you are not using the C locale for your database,
that means you must create an index on that column that uses
text_pattern_ops or varchar_pattern_ops (depending on if it is text or
varchar column) so that postgresql can use the index for that comparison,
otherwise LIKE clauses will force a sequential scan of the whole table every
time. C locale does byte by byte text comparison, so the special index
isn't required.

http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html