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

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


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

On Fri, May 11, 2012 at 1:03 PM, Carlos Mennens
<carlos.mennens (AT) gmail (DOT) com> wrote:

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

--
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

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/i...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.



--
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

Carlos Mennens wrote on 11.05.2012 21:03:
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


--
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

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?

--
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

On 05/11/2012 12:30 PM, Carlos Mennens 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.

--
Adrian Klaver
adrian.klaver (AT) gmail (DOT) com

--
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

Carlos Mennens wrote on 11.05.2012 21:30:
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.








--
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

On Fri, May 11, 2012 at 3:44 PM, Thomas Kellerer <spam_eater (AT) gmx (DOT) net> wrote:
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?

--
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

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

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.

--
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