Thread: 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
> 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
Look at the SERIAL type and the CREATE SEQUENCE documentation.
-philip
On Thu, 12 Jul 2001, Markus Jais 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
>
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/
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???
> 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
>
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
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
>
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:
> 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