Re: [GENERAL] Change SERIAL to INTEGER

[Pedro Monjo Florit]

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 [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Change SERIAL to INTEGER

[Pedro Monjo Florit]

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