Re: Finding Max Value in a Row

On Fri, May 11, 2012 at 9:03 PM, Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>wrote:

> 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.
>
> Thank you for any assistance!
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

can you do it like this (slowest way):

insert into tableName(users_id,field1,....,fieldN)
select *max*(users_id)+1 as newId, value1,.....,valueN
from tableName

you can also create (if you have create grants ) some sequence and set it's
value to maximal value max(users_id), and then insert by selecting
nextvalue as this
nextval('sequenceName'::regclass)

also you can alter user_id field of table users (if permissions are
granted) and set default value to be nextval('sequenceName'::regclass)

--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me