I'm trying to find a way to increment an invoice number. And i think it would be pretty straight forward just using a SERIAL int. But as we're selling across multiple countries, i need the invoice increment to be reset by a country_id. any good way to achieve this in a dynamic way?
Hope this is the right place to ask.
Hi,
Instead of a SERIAL field use INTEGER and different sequences for different countries. You can write a trigger which picks the right sequence based on the country_id and you can even use a trigger to making the new sequence for any new country_id.
However it might cause problems if you are not cautious enough. Sequences and serials not roll back their counter in case of a failing query, so you could introduce holes into your invoice numbers, which can make supervising authorities angry. :)