Re: Finding Max Value in a Row
От | Thomas Kellerer |
---|---|
Тема | Re: Finding Max Value in a Row |
Дата | |
Msg-id | jojp0l$g0s$1@dough.gmane.org обсуждение исходный текст |
Ответ на | Finding Max Value in a Row (Carlos Mennens <carlos.mennens@gmail.com>) |
Список | 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
В списке pgsql-sql по дате отправления: