Обсуждение: alter table serial->int
for some odd reason when i try to change a table fromserial to just plain "int with a default" postgres seems to ignore me. when i dump the schema i get ... CREATE TABLE custom ( id serial NOT NULL, name text, email text, ); then i run... alter table custom alter id type int; alter table custom alter id set default ((nextval('custom_seq'::regclass) * 100) + ((random() * (100)::double precision))::integer); after running these 2 alter table commands... i dump the schema ... and voila... nothing happened.... CREATE TABLE custom ( id serial NOT NULL, name text, email text, ); or did it? the change actually works... i see the new slightly randomized ids going in nicely my only problem is that backups aren't going to restore correctly since the schema dumps wrong
"Erik Aronesty" <erik@q32.com> writes: > for some odd reason when i try to change a table fromserial to just > plain "int with a default" postgres seems to ignore me. What PG version? I'd expect this to work somewhat sanely in 8.2, but in earlier versions fooling with the default expression for a serial column is not well supported. > alter table custom alter id type int; The fact that you even tried that suggests that you don't understand very well what "serial" is. See the manual ... regards, tom lane
-- Simpler example for you CREATE TABLE x ( id serial NOT NULL ); ALTER TABLE x alter id type int; -- Under 8.1.10, a call to pg_dump will reveal the change has not happened On Nov 8, 2007 11:13 AM, Erik Aronesty <erik@q32.com> wrote: > n Nov 7, 2007 12:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Erik Aronesty" <erik@q32.com> writes: > > > for some odd reason when i try to change a table fromserial to just > > > plain "int with a default" postgres seems to ignore me. > > > > What PG version? > > (PostgreSQL) 8.1.10 > > > The fact that you even tried that suggests that you don't understand > > very well what "serial" is. See the manual ... > > I tried to change the data type first. Which implies I know what it means. >
> "Erik Aronesty" <erik@q32.com> writes: > > for some odd reason when i try to change a table from serial to just > > plain "int with a default" postgres seems to ignore me. > > What PG version? (PostgreSQL) 8.1.10 > The fact that you even tried that suggests that you don't understand > very well what "serial" is. See the manual ... I tried to change the data type first. Which implies I know what it means.
Erik Aronesty wrote: > -- Simpler example for you > > CREATE TABLE x ( > id serial NOT NULL > ); > > ALTER TABLE x alter id type int; > > -- Under 8.1.10, a call to pg_dump will reveal the change has not happened Do an ALTER TABLE DROP DEFAULT instead. SERIAL is a macro for "int with a default". -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Eric, Don't be so defensive. Here is an abstract from documentation: "8.1.4. Serial Types The data types serial and bigserial are not true types, but merely a notational convenience for setting up unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying CREATE TABLE tablename ( colname SERIAL ); is equivalent to specifying: CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be explicitly inserted, either. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped. Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type. To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word. The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work just the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The sequence created for a serial column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression. " which proves that you don't understand "serial" data type. Igor -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Erik Aronesty Sent: Thursday, November 08, 2007 11:14 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] alter table serial->int > "Erik Aronesty" <erik@q32.com> writes: > > for some odd reason when i try to change a table from serial to just > > plain "int with a default" postgres seems to ignore me. > > What PG version? (PostgreSQL) 8.1.10 > The fact that you even tried that suggests that you don't understand > very well what "serial" is. See the manual ... I tried to change the data type first. Which implies I know what it means. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
CREATE TABLE x ( a serial NOT NULL ); alter table x alter a drop default; alter table x alter a set default 999; still doesn't help. i supposed i can dump the whole table and rebuild it.... but that's going to be a bit slow.
"Erik Aronesty" <erik@q32.com> writes: > alter table x alter a set default 999; > still doesn't help. Doesn't help what? If you mean that the sequence object is still there, you can drop that too. regression=# create table x(a serial not null); NOTICE: CREATE TABLE will create implicit sequence "x_a_seq" for serial column "x.a" CREATE TABLE regression=# drop sequence x_a_seq; NOTICE: default for table x column a depends on sequence x_a_seq ERROR: cannot drop sequence x_a_seq because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. regression=# alter table x alter a set default 999; ALTER TABLE regression=# drop sequence x_a_seq; DROP SEQUENCE regression=# \d x Table "public.x" Column | Type | Modifiers --------+---------+---------------------- a | integer | not null default 999 regards, tom lane