Thread: Auto-increment Numeric Primary keys

How can I auto-increment numeric primary keys? So on a table called
"Test1" with fields "ID" and "Name" when I do an INSERT, I only type

INSERT INTO Test1 values ('Bob');

instead of

INSERT INTO Test1 values (1, 'Bob');

Is this implemented in PostgreSQL 7?

-Vipin

Vipin Samtani wrote:
>
> How can I auto-increment numeric primary keys? So on a table called
> "Test1" with fields "ID" and "Name" when I do an INSERT, I only type
>
> INSERT INTO Test1 values ('Bob');
>
> instead of
>
> INSERT INTO Test1 values (1, 'Bob');
>
> Is this implemented in PostgreSQL 7?
>
> -Vipin

CREATE TABLE Test1 (
  id SERIAL,
  name TEXT
);

INSERT INTO Test1(name) VALUES ('Bob');

SELECT * FROM Test1;

id|name
--+----
 1|Bob
(1 row)

Hope that helps,

Mike Mascari

On Sun, 18 Jun 2000, Vipin Samtani wrote:

> How can I auto-increment numeric primary keys? So on a table called
> "Test1" with fields "ID" and "Name" when I do an INSERT, I only type
>
> INSERT INTO Test1 values ('Bob');
>
> instead of
>
> INSERT INTO Test1 values (1, 'Bob');
>
> Is this implemented in PostgreSQL 7?

You'll want to use the serial type:

CREATE TABLE Test1 (mykey SERIAL, Name varchar(10));

You will need to specify the fields when you insert:

INSERT INTO Test1(Name) Values('Bob');

However, take a look at using sequences (which is what the serial type
uses underneath).  They can give you a good bit of flxibility in terms of
starting and ending numbers, amount of increment, and so on.

Brett W. McCoy
                                              http://www.chapelperilous.net
---------------------------------------------------------------------------
Everything I like is either illegal, immoral or fattening.
        -- Alexander Woollcott