Postgres Pro
Downloads
Home   >   mailing lists

Thread: autoincrement question

autoincrement question

From
Jack Albright
Date:
I'm new to the list and new to postgreSQL.
I usually work in mySQL.
I'm using NaviCat to manipulate my postgreSQL db.

QUESTION:
What is the best way for a novice like me to  make my  primary key ID
column   auto-increment?  It is a basic integer column.

  Also, do I need to set a default value  for my primary keys?

In MySQL (using phpMyAdmin) it's a simple matter of checking a
checkbox. What is the recommended way to do this in postgres?

Thanks,

Jack Albright
jack@mobiusnm.com

Re: autoincrement question

From
"Sean Davis"
Date:


On Jan 7, 2008 3:57 PM, Jack Albright <jack@mobiusnm.com> wrote:
I'm new to the list and new to postgreSQL.
I usually work in mySQL.
I'm using NaviCat to manipulate my postgreSQL db.

QUESTION:
What is the best way for a novice like me to  make my  primary key ID
column   auto-increment?  It is a basic integer column.

 Also, do I need to set a default value  for my primary keys?

In MySQL (using phpMyAdmin) it's a simple matter of checking a
checkbox. What is the recommended way to do this in postgres?

A column of type "serial" will be a simple integer column that gets its values from a sequence, which will be automatically created.  In other words:

create table junktable (
     pk1 serial primary key
);

will get you what you want.  Check out the docs for the "serial" column type.

Sean


Re: autoincrement question

From
Richard Broersma Jr
Date:
--- On Mon, 1/7/08, Sean Davis <sdavis2@mail.nih.gov> wrote:


> create table junktable (
>      pk1 serial primary key
> );
>
> will get you what you want.  Check out the docs for the
> "serial" column type.


Also, if the table and column already exist, you can alter the table's column to become an auto-incrementing column.

http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL

You can substitute the create table statement with an appropriate alter table alter column statement from the example
demonstratedin the above link. 

Regards,
Richard Broersma Jr.

Re: autoincrement question

From
"Greg Cocks"
Date:
--------------------------------


-----Original Message-----
From: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
Sent: Monday, January 07, 2008 3:40 PM
To: Jack Albright; Sean Davis
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] autoincrement question

--- On Mon, 1/7/08, Sean Davis <sdavis2@mail.nih.gov> wrote:


> create table junktable (
>      pk1 serial primary key
> );
>
> will get you what you want.  Check out the docs for the
> "serial" column type.


|Also, if the table and column already exist, you can alter the table's
|column to become an auto-incrementing column.

|http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYP
E-|SERIAL

|You can substitute the create table statement with an appropriate alter
|table alter column statement from the example demonstrated in the above
|link.

If you are using an existing field with existing (unique) values, make
sure that you (re)set the SERIAL's index to the next value that you want
to use...