Alter Table 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 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 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: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

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

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