autoincrement???

Started by markus jaisover 24 years ago8 messagesgeneral

mjais@web.de

over 24 years ago

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with something
like "auto_increment". I do not know much about MySQL so I do not
know if this is true.

Can you please tell me, how to do this in postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

Philip Hallstrom

philip@adhesivemedia.com

over 24 years ago

In reply to: markus jais (#1)

Re: autoincrement???

Look at the SERIAL type and the CREATE SEQUENCE documentation.

-philip

On Thu, 12 Jul 2001, Markus Jais wrote:

Show quoted text

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with something
like "auto_increment". I do not know much about MySQL so I do not
know if this is true.

Can you please tell me, how to do this in postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

gregw@com-stock.com

over 24 years ago

In reply to: markus jais (#1)

Re: autoincrement???

Can you please tell me, how to do this in postgresql????

Use a SERIAL datatype:

http://postgresql.crimelabs.net/users-lounge/docs/7.1/user/datatype.html#DAT
ATYPE-SERIAL

jdearl@yahoo.com

over 24 years ago

In reply to: markus jais (#1)

Re: autoincrement???

You could either try:

CREATE TABLE address (
address_id int SERIAL,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Or you could do the same thing yourself manually with:

CREATE sequence address_id_seq;

CREATE TABLE address (
address_id int PRIMARY KEY DEFAULT
nextval('address_id_seq'),
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

I personally like the latter as it is slightly more
flexible. Plus, I often create large SQL scripts to
rebuild the database schema when I am developing and
the longer way reminds me that I need to drop the
sequence before creating the table :).

Jason

--- Markus Jais <mjais@web.de> wrote:

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253,
'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with
something
like "auto_increment". I do not know much about
MySQL so I do not
know if this is true.

Can you please tell me, how to do this in
postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please
send an appropriate
subscribe-nomail command to majordomo@postgresql.org
so that your
message can get through to the mailing list cleanly

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/

Ben-Nes Michael

miki@canaan.co.il

over 24 years ago

In reply to: markus jais (#1)

Re: autoincrement???

Use the Serial type for address_id.
And you should read the Manuals ! :)

----- Original Message -----
From: "Markus Jais" <mjais@web.de>
To: <pgsql-general@postgresql.org>
Sent: Friday, July 13, 2001 12:20 AM
Subject: [GENERAL] autoincrement???

Show quoted text

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with something
like "auto_increment". I do not know much about MySQL so I do not
know if this is true.

Can you please tell me, how to do this in postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Kevin Bullaughey

kevin@gambitdesign.com

over 24 years ago

In reply to: markus jais (#1)

RE: autoincrement???

one way to do this is with a sequence.

CREATE SEQUENCE some_seq MINVALUE 1;
CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);
INSERT INTO address VALUES(select nextval('some_seq'), 'mainstreet 12',
85253, 'munich', 'Germany');

in theory this should work but i didn't check it...it gives you the idea
anyway.

-kevin

--------------------------------------------
Kevin Bullaughey <kevin@gambitdesign.com>
Gambit Design Internet Services

Integrated domain registration and
web-based DNS management

--- http://www.gambitdesign.com/dns.html ---

Show quoted text

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Markus Jais
Sent: Thursday, July 12, 2001 5:20 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] autoincrement???

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with something
like "auto_increment". I do not know much about MySQL so I do not
know if this is true.

Can you please tell me, how to do this in postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

moomonk@daisy-chan.org

over 24 years ago

In reply to: Kevin Bullaughey (#6)

RE: autoincrement???

Huh, that's novel. I'd always written that as:

INSERT INTO address VALUES (nextval('some_seq'), 'mainstreet 12', 85253,
'munich', 'Germany');

where the difference is not using that extra 'SELECT' in the middle.

Josh

On Fri, 13 Jul 2001, Kevin Bullaughey wrote:

Show quoted text

one way to do this is with a sequence.

CREATE SEQUENCE some_seq MINVALUE 1;
CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);
INSERT INTO address VALUES(select nextval('some_seq'), 'mainstreet 12',
85253, 'munich', 'Germany');

in theory this should work but i didn't check it...it gives you the idea
anyway.

-kevin

--------------------------------------------
Kevin Bullaughey <kevin@gambitdesign.com>
Gambit Design Internet Services

Integrated domain registration and
web-based DNS management
--- http://www.gambitdesign.com/dns.html ---
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Markus Jais
Sent: Thursday, July 12, 2001 5:20 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] autoincrement???

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with something
like "auto_increment". I do not know much about MySQL so I do not
know if this is true.

Can you please tell me, how to do this in postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

col@econet.hu

over 24 years ago

In reply to: Joshua Jore (#7)

Re: autoincrement???

Hi,
try the serial type. This will create a sequence and add a default value to
collumn, which will auto increment your value.

Like:
CREATE TABLE address (
address_id serial PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany');

CoL:
col@econet.hu

Joshua Jore wrote:

Show quoted text

Huh, that's novel. I'd always written that as:

INSERT INTO address VALUES (nextval('some_seq'), 'mainstreet 12', 85253,
'munich', 'Germany');

where the difference is not using that extra 'SELECT' in the middle.

Josh

On Fri, 13 Jul 2001, Kevin Bullaughey wrote:
one way to do this is with a sequence.

CREATE SEQUENCE some_seq MINVALUE 1;
CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);
INSERT INTO address VALUES(select nextval('some_seq'), 'mainstreet 12',
85253, 'munich', 'Germany');

in theory this should work but i didn't check it...it gives you the idea
anyway.

-kevin

--------------------------------------------
Kevin Bullaughey <kevin@gambitdesign.com>
Gambit Design Internet Services

Integrated domain registration and
web-based DNS management
--- http://www.gambitdesign.com/dns.html ---
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Markus Jais
Sent: Thursday, July 12, 2001 5:20 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] autoincrement???

hi
I have the following problem:

I create the following table:

CREATE TABLE address (
address_id int PRIMARY KEY ,
street VARCHAR(40),
zipcode INT,
city VARCHAR(40),
country VARCHAR(40)
);

Now, I want the address_id to get incremented
every time I insert a value into the table.

for example:
INSERT INTO address VALUES('mainstreet 12', 85253, 'munich', 'Germany')
;
without specifying a value for the id.

a friend told me, that this works in MySQL with something
like "auto_increment". I do not know much about MySQL so I do not
know if this is true.

Can you please tell me, how to do this in postgresql????

thanks a lot
regards
markus

--
Markus Jais
http://www.mjais.de
info@mjais.de
The road goes ever on and on - Bilbo Baggins

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html