PostgreSQL Discussion - Change SERIAL to INTEGER

Author

Change SERIAL to INTEGER

Pedro Monjo Florit

2006-01-27, 7:23 am

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_se
q;

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

2006-01-27, 9:23 am

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_s
eq');

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_s
eq');

DROP SEQUENCE mytable_mytableid_se
q;

Regards
Pandu

On 1/27/06, Pedro Monjo Florit <pmonjo2000@yahoo.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_se
q;
>
> 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

Sponsored Links