To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Finding Max Value in a Row
Date: Fri, 11 May 2012 21:24:53 +0200
Lines: 34
Message-ID: <jojp0l$g0s$1@dough.gmane.org>
References: 
 <CAAQLLO6EhUi0OUi38BiH+qmgKqY+mY27_hGBQJiC5rbWorXQbw@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: 
 <CAAQLLO6EhUi0OUi38BiH+qmgKqY+mY27_hGBQJiC5rbWorXQbw@mail.gmail.com>

Carlos Mennens wrote on 11.05.2012 21:03:
> I have a problem in SQL I don't know how to solve and while I'm sure
> there are 100+ ways to do this in ANSI SQL, I'm trying to find the
> most cleanest / efficient way. I have a table called 'users' and the
> field 'users_id' is listed as the PRIMARY KEY. I know I can use the
> COUNT function, then I know exactly how many records are listed but I
> don't know what the maximum or highest numeric value is so that I can
> use the next available # for a newly inserted record. Sadly the
> architect of this table didn't feel the need to create a sequence and
> I don't know how to find the highest value.

You can get the highest value using:

   select max(users_id)
   from users;

But that method is neither safe in a multi-user environment nor fast.

But you can always assign a sequence to that column even if it wasn't done right at the start:

Create a new sequence owned by that column:

   create sequence seq_users_id
     owned by users.users_id;

Now set the value of the sequence to the current max. id:

   SELECT setval('seq_users_id', max(users_id)) FROM users;

And finally make the users_id column use the sequence for the default value:

   alter table users alter column users_id set default nextval('seq_users_id');

Thomas