Date: Thu, 14 Aug 2003 09:12:00 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: independent sequence for each month
Message-ID: <20030814141200.GG27870@wolff.to>
Mail-Followup-To: Tomasz Myrta <jasiek@klaster.net>,
	PGSQL-SQL <pgsql-sql@postgresql.org>
References: <3F3B29C6.6050101@klaster.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3F3B29C6.6050101@klaster.net>
User-Agent: Mutt/1.5.4i

On Thu, Aug 14, 2003 at 08:18:46 +0200,
  Tomasz Myrta <jasiek@klaster.net> wrote:
> Hi
> I have to generate unique numbers starting from 1 every month. I can't 
> reset sequence on last day of month, because sometimes I have to insert 
> some future or past values. I thought I can create independent sequence 
> for each month, but it doesn't sound elegant.

If you are restarting from 1 each month, I suspect that you also want
consecutive nmumbers without any gaps. If so, then using sequences
may not work for you.

If your server isn't heavily loaded you can lock the table and select
the highest number used in the current month (using coalesce to change
null to 0) and add 1 to it in your insert statement.

Another option is if the numbers only appear on reports and are not used
in the database, is to have the application generate them. If the reports
are just listings of the complete list of monthly events this will probably
be easy.