From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Finding Max Value in a Row |
Date: | 2012-05-11 19:24:53 |
Message-ID: | jojp0l$g0s$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Mennens | 2012-05-11 19:30:27 | Re: Finding Max Value in a Row |
Previous Message | David Johnston | 2012-05-11 19:20:16 | Re: Finding Max Value in a Row |