Re: altering a table to set serial function - Mailing list pgsql-general

Prabu Subroto <prabu_subroto@yahoo.com> writes:

> If I read your suggestion, that means...I have drop
> the column "salesid" and re-create the column
> "salesid". and it means, I will the data in the
> current "salesid" column.
>
> Do you have further suggestion?

You can do it "by hand" without dropping the column:

CREATE SEQUENCE salesid_seq;
SELECT setval('salesid_seq', (SELECT max(salesid) FROM sales) + 1);
ALTER TABLE sales ALTER COLUMN salesid DEFAULT nextval('salesid_seq');

This is the same thing that the SERIAL datatype does "behind the
scenes".

I can't vouch for the exact syntax of the above but that should get
you started.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863