From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Alter Table Auto_Increment |
Date: | 2010-09-20 14:08:39 |
Message-ID: | AANLkTimv2z1+KJ_JemLVOSYPja+ak0oD_7WvP-NjEaBf@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2010-09-20 14:16:58 | Re: where does postgres keep the query result until it is returned? |
Previous Message | Willy-Bas Loos | 2010-09-20 14:06:35 | Re: where does postgres keep the query result until it is returned? |