Re: Change SERIAL to INTEGER

Pandurangan R S wrote:
> Hi,
>
> Is there any way to change a SERIAL type to an INTEGER? I think that it
> should be easy, since SERIAL is, in fact, an INTEGER with some conditions.
>
> There serial column is just a integer, with default as nextval from a
> sequence, so there is no neccessity to change the datatype of the
> column. You have successfully removed the default value for this
> column with you first SQL statement.
>
> I hope this condition should not cause any problem, except that
> sequence lingers in the database.
>
> However, if you are determined to get rid of the sequence, then you
> may follow the procedure below
>
> Disclaimer : It worked for me and I hope that this tinkering with
> pg_depend table will not cause any adverse effects and you might want
> to make sure that there will not be any side effects and I am not
> responsible for any damage caused :)
>
> select * from pg_depend where refobjid = (select oid from pg_class
> where relname = 'mytable') and objid = (select oid from pg_class where
> relname = 'mytable_mytableid_seq');
>
> delete from pg_depend where refobjid = (select oid from pg_class where
> relname = 'mytable') and objid = (select oid from pg_class where
> relname = 'mytable_mytableid_seq');
>
> DROP SEQUENCE mytable_mytableid_seq;
>
> Regards
> Pandu
>
> On 1/27/06, Pedro Monjo Florit <pmonjo2000@yahoo.com> wrote:

>> but the last command fails, saying that mytable still uses this sequence.
>>
>> Is there any way to change a SERIAL type to an INTEGER? I think that it
>> should be easy, since SERIAL is, in fact, an INTEGER with some conditions.
>>

Hi Pandu,

I have finally had some time to try your suggestion and it seems to work
perfectly.

Thanks,

Pedro