Re: 'SERIAL' in pgsql

On 16 July 2012 10:23, Archana K N <archanakknn(at)gmail(dot)com> wrote:
>
> Hello,
>
> I have a database whose primary key is 'slno' and which is an auto
> increment field.I inserted some values to database say upto
> " slno - 5". Then I deleted the row with 'slno' 3 . Now I want the to shift
> all the rows upward ie 4th row should be third and its 'slno' should also be
> 3. I heard that this is not possible.Is there a way to do this other than to
> move all rows to another database except the slno.

No, you can't do that. The SERIAL data type is really short-hand for
"make this column using type INTEGER, make a new sequence that will be
owned by this column and set this column's default value to get the
next value from that sequence".

If you really want the row's position within the result set, you can
use a window function, like:

SELECT row_number() OVER (ORDER BY primary_key_column_name ASC), *
FROM my_table
ORDER BY primary_key_column_name ASC;

Or you may wish to push the main query into a subquery and get a
row_number out of that so that you don't have to duplicate the ORDER
BY clause:

SELECT row_number() OVER (), x.*
FROM (
SELECT *
FROM my_table
ORDER BY primary_key_column_name ASC
) x;

See http://www.postgresql.org/docs/current/static/tutorial-window.html
and http://www.postgresql.org/docs/current/static/functions-window.html
for more info.

Regards

Thom