pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConvert serial column to regular integer
4+ messages / 3 participants
[nested] [flat]
* Convert serial column to regular integer
@ 2007-05-11 16:24 Collin Peters <cadiolis@gmail.com>
2007-05-11 16:36 ` Re: Convert serial column to regular integer Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Collin Peters @ 2007-05-11 16:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert serial column to regular integer
2007-05-11 16:24 Convert serial column to regular integer Collin Peters <cadiolis@gmail.com>
@ 2007-05-11 16:36 ` Rodrigo De León <rdeleonp@gmail.com>
2007-05-11 20:18 ` Re: Convert serial column to regular integer Collin Peters <cadiolis@gmail.com>
2007-05-11 20:32 ` Re: Convert serial column to regular integer Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Rodrigo De León @ 2007-05-11 16:36 UTC (permalink / raw)
To: pgsql-sql; +Cc: Collin Peters <cadiolis@gmail.com>
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert serial column to regular integer
2007-05-11 16:24 Convert serial column to regular integer Collin Peters <cadiolis@gmail.com>
2007-05-11 16:36 ` Re: Convert serial column to regular integer Rodrigo De León <rdeleonp@gmail.com>
@ 2007-05-11 20:18 ` Collin Peters <cadiolis@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Collin Peters @ 2007-05-11 20:18 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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;
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert serial column to regular integer
2007-05-11 16:24 Convert serial column to regular integer Collin Peters <cadiolis@gmail.com>
2007-05-11 16:36 ` Re: Convert serial column to regular integer Rodrigo De León <rdeleonp@gmail.com>
@ 2007-05-11 20:32 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-05-11 20:32 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: pgsql-sql@postgresql.org, "Collin Peters" <cadiolis@gmail.com>
"=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-05-11 20:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-11 16:24 Convert serial column to regular integer Collin Peters <cadiolis@gmail.com>
2007-05-11 16:36 ` Rodrigo De León <rdeleonp@gmail.com>
2007-05-11 20:18 ` Collin Peters <cadiolis@gmail.com>
2007-05-11 20:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox