Thread: Alter Table Auto_Increment
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;
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 -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Mon, Sep 20, 2010 at 10:08 AM, Thom Brown <thom@linux.com> wrote: > 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 That appeared to work fine. My question that I can't find in the documentation is that if I already have one row created where my 'id = 1' for AUTO_INCREMENT, how do I enter future values into my table and force id to auto_increment rather than having me type the value into the SQL command? This way I can let PostgreSQL add the next INTEGER rather than me having to know and avoid duplicates. INSERT INTO blades VALUES ( '2', 'data', 'data', 'data', '1234', '2010-09-20 ); Is there a way I can omit having to type the '2' above in my SQL command if 'id' column is configured for AUTO_INCREMENT
On Mon, Sep 20, 2010 at 2:43 PM, A.M. <agentm@themactionfaction.com> wrote: > DEFAULT Sorry just to be clear you're saying that I need to enter the command as: INSERT INTO table_name DEFAULT VALUES ( 'data', 'data', 'data', 'data', '2010-09-20' ); Or does the 'DEFAULT' value go in ( )?
On 20 September 2010 19:40, Carlos Mennens <carlos.mennens@gmail.com> wrote: > On Mon, Sep 20, 2010 at 10:08 AM, Thom Brown <thom@linux.com> wrote: >> 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 > > That appeared to work fine. My question that I can't find in the > documentation is that if I already have one row created where my 'id = > 1' for AUTO_INCREMENT, how do I enter future values into my table and > force id to auto_increment rather than having me type the value into > the SQL command? This way I can let PostgreSQL add the next INTEGER > rather than me having to know and avoid duplicates. > > INSERT INTO blades VALUES ( > '2', > 'data', > 'data', > 'data', > '1234', > '2010-09-20 > ); > > Is there a way I can omit having to type the '2' above in my SQL > command if 'id' column is configured for AUTO_INCREMENT You need to specify the columns you're inserting into: INSERT INTO blades (ilom_ip, host_os, host_ip, hostname, "serial", gfe, admin) VALUES ('value', 'value', 'value', 'value', 'value', 'value', 'value'); The id column will then pick up its default value. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On 09/20/2010 11:40 AM, Carlos Mennens wrote: > On Mon, Sep 20, 2010 at 10:08 AM, Thom Brown<thom@linux.com> wrote: >> 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 > > That appeared to work fine. My question that I can't find in the > documentation is that if I already have one row created where my 'id = > 1' for AUTO_INCREMENT, how do I enter future values into my table and > force id to auto_increment rather than having me type the value into > the SQL command? This way I can let PostgreSQL add the next INTEGER > rather than me having to know and avoid duplicates. > > INSERT INTO blades VALUES ( > '2', > 'data', > 'data', > 'data', > '1234', > '2010-09-20 > ); > > Is there a way I can omit having to type the '2' above in my SQL > command if 'id' column is configured for AUTO_INCREMENT > INSERT INTO blades VALUES ( DEFAULT, 'data', 'data', 'data', '1234', '2010-09-20 ); -- Adrian Klaver adrian.klaver@gmail.com
On 20 September 2010 19:54, Carlos Mennens <carlos.mennens@gmail.com> wrote: > On Mon, Sep 20, 2010 at 2:43 PM, A.M. <agentm@themactionfaction.com> wrote: >> DEFAULT > > Sorry just to be clear you're saying that I need to enter the command as: > > INSERT INTO table_name DEFAULT VALUES ( > 'data', > 'data', > 'data', > 'data', > '2010-09-20' > ); > Well, you can use the DEFAULT value if you wish: INSERT INTO table_name ( DEFAULT, 'data', 'data', 'data', 'data', '2010-09-20' ); Where the keyword DEFAULT appears, it will, as you'd expect, use the default value for that column. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Thanks all! I understand the concept now.
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. merlin
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