Re: Change SERIAL to INTEGER

Hi everybody:

In a computer which is un production, I have a PostgreSQL 7.4 database.
In a couple of tables, I have just realised that I made a small mistake:
the primary key is SERIAL but what I really need is an INTEGER, since I
do not need the auto-increment feature or the sequence. Until now, this
has not created any problem, since all INSERTs set the primary key
value, but I am not sure if in the future this could cause any problem.
I cannot drop and recreate the table or the column, since this column is
a used as foreign keys in other tables.

I have already tried to do the following:

ALTER TABLE mytable ALTER COLUMN mytableid DROP DEFAULT;
DROP SEQUENCE mytable_mytableid_seq;

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.

Thanks!

Pedro

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(at)yahoo(dot)com> wrote:
> Hi everybody:
>
> In a computer which is un production, I have a PostgreSQL 7.4 database.
> In a couple of tables, I have just realised that I made a small mistake:
> the primary key is SERIAL but what I really need is an INTEGER, since I
> do not need the auto-increment feature or the sequence. Until now, this
> has not created any problem, since all INSERTs set the primary key
> value, but I am not sure if in the future this could cause any problem.
> I cannot drop and recreate the table or the column, since this column is
> a used as foreign keys in other tables.
>
> I have already tried to do the following:
>
> ALTER TABLE mytable ALTER COLUMN mytableid DROP DEFAULT;
> DROP SEQUENCE mytable_mytableid_seq;
>
> 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.
>
> Thanks!
>
> Pedro
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

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(at)yahoo(dot)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