Обсуждение: Alter Table Auto_Increment

Поиск
Список
Период
Сортировка

Alter Table Auto_Increment

От
Carlos Mennens
Дата:
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;

Re: Alter Table Auto_Increment

От
Thom Brown
Дата:
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

Re: Alter Table Auto_Increment

От
Carlos Mennens
Дата:
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

Re: Alter Table Auto_Increment

От
Carlos Mennens
Дата:
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 ( )?

Re: Alter Table Auto_Increment

От
Thom Brown
Дата:
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

Re: Alter Table Auto_Increment

От
Adrian Klaver
Дата:
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

Re: Alter Table Auto_Increment

От
Thom Brown
Дата:
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

Re: Alter Table Auto_Increment

От
Carlos Mennens
Дата:
Thanks all! I understand the concept now.

Re: Alter Table Auto_Increment

От
Merlin Moncure
Дата:
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

Re: Alter Table Auto_Increment

От
Thom Brown
Дата:
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

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.