Обсуждение: Finding Max Value in a Row

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!

<br /><br /><div class="gmail_quote">On Fri, May 11, 2012 at 9:03 PM, Carlos Mennens <span dir="ltr"><<a
href="mailto:carlos.mennens@gmail.com"target="_blank">carlos.mennens@gmail.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> I have a problem in SQL I
don'tknow how to solve and while I'm sure<br /> there are 100+ ways to do this in ANSI SQL, I'm trying to find the<br
/>most cleanest / efficient way. I have a table called 'users' and the<br /> field 'users_id' is listed as the PRIMARY
KEY.I know I can use the<br /> COUNT function, then I know exactly how many records are listed but I<br /> don't know
whatthe maximum or highest numeric value is so that I can<br /> use the next available # for a newly inserted record.
Sadlythe<br /> architect of this table didn't feel the need to create a sequence and<br /> I don't know how to find the
highestvalue.<br /><br /> Thank you for any assistance!<br /><span class="HOEnZb"><font color="#888888"><br /> --<br />
Sentvia pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make
changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></span></blockquote></div><br /><br
clear="all"/>can you do it like this (slowest way):<br /><br />insert into tableName(users_id,field1,....,fieldN)<br
/>select<b>max</b>(users_id)+1 as newId, value1,.....,valueN<br />from tableName<br /><br /><br />you can also create
(ifyou have create grants ) some sequence and set it's value to maximal value max(users_id), and then insert by
selectingnextvalue as this<br />nextval('sequenceName'::regclass)<br /><br />also you can alter user_id field of table
users(if permissions are granted) and set default value to be nextval('sequenceName'::regclass)<br /><br />-- <br
/>---------------------------------------<br/>Viktor Bojović<br /> ---------------------------------------<br
/>WhereverI go, Murphy goes with me<br /> 

On Fri, May 11, 2012 at 1:03 PM, Carlos Mennens
<carlos.mennens@gmail.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.


select max(row_name) as max_val from table;

Why not create a sequence on the current data then use currval() to
get the max value.

-wes

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Carlos Mennens
> Sent: Friday, May 11, 2012 3:04 PM
> To: PostgreSQL (SQL)
> Subject: [SQL] Finding Max Value in a Row
> 
> 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!
> 

Finding the MAXimium of a given set of data is an aggregate operation and so
you should look in the functions section of the documentation under
"Aggregate"

http://www.postgresql.org/docs/9.0/interactive/functions-aggregate.html

Assuming that the users_id field is an integer:

SELECT MAX(users_id) FROM users; --NO GROUP BY needed since no other fields
are being output...

That said, you really should create and attach a sequence so that you can
avoid race/concurrency issues.

David J.

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

Thanks for all the help thus far everyone! I sadly didn't
create/design the table and would love to create a SEQUENCE on that
particular field but not sure how unless I DROP the table and create
from scratch.

Currently the data TYPE on the primary key field (users_id) is CHAR
and I have no idea why...it should be NUMERIC or SERIAL but it's not
so my question is if I want to ALTER the column and create a sequence,
would I simply do:

ALTER TABLE users
ALTER COLUMN users_id TYPE serial
;

Obviously if any of the data stored in users_id is actual CHAR, I'm
guessing the database would reject that request to change type as the
existing data would match. However the data type is CHAR but the field
values are all numeric from 1000000010 - 1000000301 so I'm hoping that
would work for SERIAL which is just INTEGER, right?

Carlos Mennens wrote on 11.05.2012 21:30:
> Thanks for all the help thus far everyone! I sadly didn't
> create/design the table and would love to create a SEQUENCE on that
> particular field but not sure how unless I DROP the table and create
> from scratch.
>
> Currently the data TYPE on the primary key field (users_id) is CHAR
> and I have no idea why...it should be NUMERIC or SERIAL but it's not
> so my question is if I want to ALTER the column and create a sequence,
> would I simply do:
>
> ALTER TABLE users
> ALTER COLUMN users_id TYPE serial
> ;
>
> Obviously if any of the data stored in users_id is actual CHAR, I'm
> guessing the database would reject that request to change type as the
> existing data would match. However the data type is CHAR but the field
> values are all numeric from 1000000010 - 1000000301 so I'm hoping that
> would work for SERIAL which is just INTEGER, right?

Use this:

alter table users    alter column users_id type integer using to_number(users_id, '99999');

(Adjust the '99999' to the length of the char column)

Then create and "assign" the new sequence as I have shown in my other post.



 

On 05/11/2012 12:30 PM, Carlos Mennens wrote:
> Thanks for all the help thus far everyone! I sadly didn't
> create/design the table and would love to create a SEQUENCE on that
> particular field but not sure how unless I DROP the table and create
> from scratch.
>
> Currently the data TYPE on the primary key field (users_id) is CHAR
> and I have no idea why...it should be NUMERIC or SERIAL but it's not
> so my question is if I want to ALTER the column and create a sequence,
> would I simply do:
>
> ALTER TABLE users
> ALTER COLUMN users_id TYPE serial
> ;
>
> Obviously if any of the data stored in users_id is actual CHAR, I'm
> guessing the database would reject that request to change type as the
> existing data would match. However the data type is CHAR but the field
> values are all numeric from 1000000010 - 1000000301 so I'm hoping that
> would work for SERIAL which is just INTEGER, right?
>

Well the question to ask is if it is declared CHAR was that done for a 
legitimate reason? One reason I can think of is to have leading 0s in a 
'number'. Might want to double check that code downstream is not 
depending on CHAR behavior.

-- 
Adrian Klaver
adrian.klaver@gmail.com

On Fri, May 11, 2012 at 3:44 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Use this:
>
> alter table users
>    alter column users_id type integer using to_number(users_id, '99999');
>
> (Adjust the '99999' to the length of the char column)

When you wrote "Adjust the '99999' to the length of the char column,
do you mean change '99999' to '312' if my last used maximum value was
312? So the next sequence primary key value would be '313', right?

On Fri, May 11, 2012 at 3:43 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

> Well the question to ask is if it is declared CHAR was that done for a
> legitimate reason? One reason I can think of is to have leading 0s in a
> 'number'. Might want to double check that code downstream is not depending
> on CHAR behavior.

Very good question and asked by myself to the original SQL author and
he explained while he didn't use the most efficient data types, he
used ones "he" felt would be more transparent across a multitude of
RDBMS vendors. So the answer is no, it would not be an issue
considering I use and will always use PostgreSQL. If someone else uses
a different vendor, they can manage that import/export process then.

Carlos Mennens wrote on 11.05.2012 21:50:
> On Fri, May 11, 2012 at 3:44 PM, Thomas Kellerer<spam_eater@gmx.net>  wrote:
>> Use this:
>>
>> alter table users
>>     alter column users_id type integer using to_number(users_id, '99999');
>>
>> (Adjust the '99999' to the length of the char column)
>
> When you wrote "Adjust the '99999' to the length of the char column,
> do you mean change '99999' to '312' if my last used maximum value was
> 312? So the next sequence primary key value would be '313', right?
>
No, the number of 9's defined the number of digits in the numbers.
There must not be less 9's in the format mask than the number of digits in the highest value.

The above example would create wrong values if the highest "number" was 100000

Check the manual about details on the format mask for to_number()

Carlos Mennens wrote on 11.05.2012 21:53: 
> Very good question and asked by myself to the original SQL author and
> he explained while he didn't use the most efficient data types, he
> used ones "he" felt would be more transparent across a multitude of
> RDBMS vendors. So the answer is no, it would not be an issue
> considering I use and will always use PostgreSQL. If someone else uses
> a different vendor, they can manage that import/export process then.

You should tell those people that char is a bad choice in _any_ DBMS due to the padding that is involved.

varchar would have been slightly better.

But it's never, ever a good idea to store numbers in a character column.
*Every* RDBMS has some kind of integer datatype (they might just have different names).

 

you can convert from type to type using ::varchar or ::char(size) or ::integer<br />so you can use sequence but you
willhave to convert it's result to suitable type (that can also be put in default value of user_id attribute)<br /><br
/><divclass="gmail_quote">On Fri, May 11, 2012 at 9:30 PM, Carlos Mennens <span dir="ltr"><<a
href="mailto:carlos.mennens@gmail.com"target="_blank">carlos.mennens@gmail.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> Thanks for all the help thus
fareveryone! I sadly didn't<br /> create/design the table and would love to create a SEQUENCE on that<br /> particular
fieldbut not sure how unless I DROP the table and create<br /> from scratch.<br /><br /> Currently the data TYPE on the
primarykey field (users_id) is CHAR<br /> and I have no idea why...it should be NUMERIC or SERIAL but it's not<br /> so
myquestion is if I want to ALTER the column and create a sequence,<br /> would I simply do:<br /><br /> ALTER TABLE
users<br/> ALTER COLUMN users_id TYPE serial<br /> ;<br /><br /> Obviously if any of the data stored in users_id is
actualCHAR, I'm<br /> guessing the database would reject that request to change type as the<br /> existing data would
match.However the data type is CHAR but the field<br /> values are all numeric from 1000000010 - 1000000301 so I'm
hopingthat<br /> would work for SERIAL which is just INTEGER, right?<br /><span class="HOEnZb"><font
color="#888888"><br/> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></span></blockquote></div><br/><br clear="all" /><br />-- <br />---------------------------------------<br
/>ViktorBojović<br />---------------------------------------<br />Wherever I go, Murphy goes with me<br /> 

On 2012-05-11, Carlos Mennens <carlos.mennens@gmail.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.

If you need pecisely the next value a sequence won't get you there anyway.



select max(id) from users;

that's only going to be reliable if you have a single concurent database user
doing inserts.



>
> Thank you for any assistance!
>


-- 
⚂⚃ 100% natural

On Fri, May 11, 2012 at 4:42 PM, Viktor Bojović
<viktor.bojovic@gmail.com> wrote:
> you can convert from type to type using ::varchar or ::char(size) or
> ::integer
> so you can use sequence but you will have to convert it's result to suitable
> type (that can also be put in default value of user_id attribute)

I'm not understanding why I'm not able to change this column type from
char to integer? There are no non-numeric existing characters stored
in this particular column (cust_id). I've gone so far as to delete the
foreign key and primary key that associated with this column (cust_id)
but still I get a generic error:

forza=# \d customers            Table "public.customers"   Column    |          Type          | Modifiers
--------------+------------------------+-----------cust_id      | character(10)          |cust_name    | character
varying(100)| not nullcust_address | character(50)          |cust_city    | character(50)          |cust_state   |
character(5)          |cust_zip     | character(10)          |cust_country | character(50)          |cust_contact |
character(50)         |cust_email   | character(255)         | 

All the values in the column in question:

forza=# SELECT cust_id
forza-# FROM customers
forza-# ORDER BY cust_id; cust_id
------------1000110002100031000410005
(5 rows)

forza=# ALTER TABLE customers
ALTER COLUMN cust_id TYPE integer;
ERROR:  column "cust_id" cannot be cast to type integer

When I view the logs in  /var/log/postgresql.log, I see the same exact
error printed above so I can only assume the problem is invalid SQL
statement or I'm breaking some ANSI SQL rule. Can someone please help
me understand how I can change the data type for this column? I've
deleted the primary key constraint so I don't know if that was a good
/ bad idea. Thanks for any info / help!

-Carlos

Carlos Mennens <carlos.mennens@gmail.com> writes:
> I'm not understanding why I'm not able to change this column type from
> char to integer?

> forza=# ALTER TABLE customers
> ALTER COLUMN cust_id TYPE integer;
> ERROR:  column "cust_id" cannot be cast to type integer

Try "ALTER ... cust_id TYPE integer USING cust_id::integer".

If you don't specify a USING expression, the command requires an
implicit coercion from one type to the other, and there is none from
char(n) to int.  You can force it with an explicit coercion, though.

It strikes me that "cannot be cast" is a poor choice of words here,
since the types *can* be cast if you try.  Would it be better if the
message said "cannot be cast implicitly to type foo"?  We could also
consider a HINT mentioning use of USING.
        regards, tom lane