On 20 September 2010 20:58, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Sep 20, 2010 at 10:08 AM, Thom Brown <thom@linux.com> wrote:
>> On 20 September 2010 14:53, Carlos Mennens <carlos.mennens@gmail.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.
Ah yes, that's a very good point.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935