To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Finding Max Value in a Row
Date: Fri, 11 May 2012 22:00:52 +0200
Lines: 22
Message-ID: <jojr3q$uof$1@dough.gmane.org>
References: 
 <CAAQLLO6EhUi0OUi38BiH+qmgKqY+mY27_hGBQJiC5rbWorXQbw@mail.gmail.com>
	<00ee01cd2fab$1b528b80$51f7a280$@yahoo.com>
	<CAAQLLO7-O26xpisutPoud__iDaco6WtZCr6CBG5OgiwpgvWd6g@mail.gmail.com>
	<jojq4e$ogj$1@dough.gmane.org>
	<CAAQLLO5x8_kxpM8epdqjh7UPG7tTX8i0w04fODZd1F+8bKdr0A@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: 
 <CAAQLLO5x8_kxpM8epdqjh7UPG7tTX8i0w04fODZd1F+8bKdr0A@mail.gmail.com>

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