Thread: Convert serial column to regular integer
I have a need to convert an incorrectly typed serial column to a regular integer column. Basically this just involves removing the sequence. I am able to successfully remove the default value (DROP DEFAULT) (which seems to use nextval) and now pgadmin does show the column as an integer, but I cannot remove the sequence as it says it is still in use. If I look at the column in pgadmin the sequence field is still filled in with the sequence but it is grayed out. Is there any way to remove the sequence fully? Regards, Collin
On 5/11/07, Collin Peters <cadiolis@gmail.com> wrote: > I have a need to convert an incorrectly typed serial column to a > regular integer column. Basically this just involves removing the > sequence. I am able to successfully remove the default value (DROP > DEFAULT) (which seems to use nextval) and now pgadmin does show the > column as an integer, but I cannot remove the sequence as it says it > is still in use. If I look at the column in pgadmin the sequence > field is still filled in with the sequence but it is grayed out. Is > there any way to remove the sequence fully? > > Regards, > Collin CREATE TABLE dtab (i SERIAL); ALTER TABLE dtab ALTER COLUMN i DROP DEFAULT; ALTER SEQUENCE dtab_i_seq OWNED BY NONE; DROP SEQUENCE dtab_i_seq;
Anything pre-8.2? On 5/11/07, Rodrigo De León <rdeleonp@gmail.com> wrote: > > CREATE TABLE dtab (i SERIAL); > > ALTER TABLE dtab ALTER COLUMN i DROP DEFAULT; > > ALTER SEQUENCE dtab_i_seq OWNED BY NONE; > > DROP SEQUENCE dtab_i_seq; >
"Rodrigo De León" <rdeleonp@gmail.com> writes: > On 5/11/07, Collin Peters <cadiolis@gmail.com> wrote: >> Is there any way to remove the sequence fully? > ALTER SEQUENCE dtab_i_seq OWNED BY NONE; Pre-8.2 that command doesn't exist, but you can get the same effect if you manually remove the pg_depend entry that links the sequence to the column. Be sure you're removing the right entry ;-) regards, tom lane