Re: Add a serial column to a table based on a sort clause

I have a table, which has a creation_ts (timestamp) column, but does not
have a id (serial) column. I want to add such a one, but, AFAIK, if I enter

ALTER TABLE table ADD COLUMN id serial

it will randomly put the sequence numbers.

I wrote a function, which uses a cursor and UPDATE WHERE CURRENT OF, and
it works, but is there any other, more "elegant", way?

CREATE OR REPLACE FUNCTION newsfeed_seq_add_cursor
() RETURNS void AS $$
DECLARE
curs refcursor;
rec record;
BEGIN
create sequence seq;
ALTER TABLE table ADD COLUMN id int;
OPEN curs FOR SELECT * FROM table ORDER BY creation_ts FOR UPDATE;
FETCH curs INTO rec;
WHILE FOUND IS TRUE
LOOP
UPDATE table SET id = nextval('seq') WHERE CURRENT OF curs;
END LOOP;
ALTER TABLE table ALTER COLUMN id SET NOT NULL, ALTER COLUMN id
SET DEFAULT nextval('seq');
END;
$$ language plpgsql;

On Wed, Sep 02, 2009 at 04:40:13PM +0400, Igor Katson wrote:
> I have a table, which has a creation_ts (timestamp) column, but does
> not have a id (serial) column. I want to add such a one, but, AFAIK,
> if I enter
>
> ALTER TABLE table ADD COLUMN id serial
>
> it will randomly put the sequence numbers.

"Random" is how you should think of them. Sequences guarantee only
uniqueness. Neither order nor gap-less numbers, nor any other
property apply to them.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate