Re: [GENERAL] 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 <[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
Re: [GENERAL] Change SERIAL to INTEGER
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: > 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 > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq