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.mennens@gmail.com>  wrote:
>> On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell<rod@iol.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@iol.ie