That's why you need to do this inside a function. Basically just make an insert function for the table and have it calculate the count and do the insert in one transaction.
On Tue, Jul 5, 2011 at 5:41 PM, Samuel Gendler <sgend...@ideasculptor.com> wrote: > > > On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani <jo...@jfcomputer.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 > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql