From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com> |
Cc: | Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Alter Table Auto_Increment |
Date: | 2010-09-20 19:58:18 |
Message-ID: | AANLkTikcPW8L7HZGqtx6BQsPaspLuTB_syNXZ7hEiXGT@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Sep 20, 2010 at 10:08 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 20 September 2010 14:53, Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com> wrote:
>> I have a table in my database and would like to modify the one column
>> that is already configured to be the PRIMARY KEY but I forgot to set
>> it for AUTO_INCREMENT. For some reason I can't find what the proper
>> command would be in the documentation and my commands from MySQL don't
>> appear to work properly in PostgreSQL:
>>
>>
>> sun=# \d blades
>> Table "public.blades"
>> Column | Type | Modifiers
>> ----------+-----------------------+-----------
>> id | integer | not null
>> ilom_ip | character varying(15) |
>> host_os | character varying(50) |
>> host_ip | character varying(15) |
>> hostname | character varying(50) |
>> serial | character varying(30) |
>> gfe | character varying(10) |
>> admin | character varying(50) |
>> Indexes:
>> "blades_pkey" PRIMARY KEY, btree (id)
>>
>> My command is not working so I don't know what I am doing wrong:
>>
>> sun=# ALTER TABLE blades MODIFY int AUTO_INCREMENT;
>> ERROR: syntax error at or near "MODIFY"
>> LINE 1: ALTER TABLE blades MODIFY int AUTO_INCREMENT;
>>
>
> That's MySQL syntax. Usually you'd just use the SERIAL datatype which
> automatically creates a sequence. But since you've already made the
> table, you can create it manually:
>
> CREATE SEQUENCE seq_blades_id;
> SELECT setval('seq_blades_id', max(id)) FROM blades; -- set the
> sequence's value to the maximum value of "id"
> ALTER TABLE blades ALTER COLUMN id SET DEFAULT
> nextval('seq_blades_id'); -- make default value get value from
> sequence
note: If you want the sequence to drop when the controlling table
drops, you want to do like this:
create sequence seq_blades_id owned by blades.id;
This is almost always a good idea if the sequence is used by one and
only one table. The magic 'serial' type does this for you.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-09-20 19:59:33 | Re: Alter Table Auto_Increment |
Previous Message | Bryan Murphy | 2010-09-20 19:53:12 | Re: pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working |