Re: Remove Modifiers on Table

On 17/05/2011 19:07, Carlos Mennens wrote:
> On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens
> <carlos(dot)mennens(at)gmail(dot)com> wrote:
>> On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell<rod(at)iol(dot)ie> wrote:
>>> Yes, that's exactly right - SERIAL does it all for you. The mistake some
>>> people make, on the other hand, is thinking that SERIAL is a type in its own
>>> right - it's not, it just does all those steps automatically.
>
> So if I have an existing column in my table with a INT data type, I
> can't seem to understand how to convert this on my 8.4 production
> server:
>
> ALTER TABLE users ALTER COLUMN id TYPE SERIAL;
> ERROR: type "serial" does not exist

That's because of what I just mentioned above. :-) It's not a type: it's
just a shortcut. What you need to do instead is something like this:

-- Create the sequence.
create sequence users_id_seq;

-- Tell the column to pull default values from the sequence.
alter table users alter column id set default nextval('users_id_seq');

-- Establish a dependency between the column and the sequence.
alter sequence users_id_seq owned by users.id;

HTH

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie