pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedauto_increment?
27+ messages / 22 participants
[nested] [flat]
* auto_increment?
@ 1999-10-21 15:44 Rodrigo Bernardo Pimentel <rbp@pobox.com>
0 siblings, 2 replies; 27+ messages in thread
From: Rodrigo Bernardo Pimentel @ 1999-10-21 15:44 UTC (permalink / raw)
To: pgsql-sql
Hi.
I recently had to port a script from using Postgres to MySQL.
In Postgres, when I have to use a unique id, I create a sequence
and call nextval('sequence') each time I insert a new line in the table.
MySQL doesn't have sequences (as far as the documentation shows).
So I had to look for an alternative. I found an atribute "auto_increment"
which seems pretty interesting. If I do
CREATE TABLE table (
id int4 primary key auto_increment,
name text,
email text);
It automatically assigns the next value when I do
INSERT INTO table (nome, email) values ('My name', 'me@somewhere.com');
When I noticed MySQL didn't have sequences, I cursed a lot. When I
found this out, though, I kept wondering if Postgres also had it, but I
couldn't find any mention in the documentation. Does anyone know if something
similar exists? Am I doing thins the hard way? Is there any other way of
automagically getting unique ids?
TIA,
rbp
________________________________________________________________________
Rodrigo Bernardo Pimentel <rbp@pobox.com>| GPG KeyID: 81F85A48
LinuxSP <http://www.linuxsp.org.br;| Fingerprint:
AirGeeks <http://www.airgeeks.org;|7E62 9CA2 C95B FC86 B334
_____________ Debian Linux User ______________|203E C011 2E4D 81F8 5A48
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [SQL] auto_increment?
@ 1999-10-21 16:12 David Rugge <davidrugge@mindspring.com>
parent: Rodrigo Bernardo Pimentel <rbp@pobox.com>
1 sibling, 0 replies; 27+ messages in thread
From: David Rugge @ 1999-10-21 16:12 UTC (permalink / raw)
To: Rodrigo Bernardo Pimentel <rbp@pobox.com>; pgsql-sql
On Thu, 21 Oct 1999, you wrote:
> When I noticed MySQL didn't have sequences, I cursed a lot. When I
>found this out, though, I kept wondering if Postgres also had it, but I
>couldn't find any mention in the documentation. Does anyone know if something
>similar exists? Am I doing thins the hard way? Is there any other way of
>automagically getting unique ids?
> TIA,
Use the serial datatype. It automatically creates a sequence for you that
increments each time you do an insert,
--
-David Rugge
http://www.mindspring.com/~davidrugge/index.html
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [SQL] auto_increment?
@ 1999-10-21 16:12 Oleg Broytmann <phd@phd.russ.ru>
parent: Rodrigo Bernardo Pimentel <rbp@pobox.com>
1 sibling, 0 replies; 27+ messages in thread
From: Oleg Broytmann @ 1999-10-21 16:12 UTC (permalink / raw)
To: Rodrigo Bernardo Pimentel <rbp@pobox.com>; +Cc: pgsql-sql
On Thu, 21 Oct 1999, Rodrigo Bernardo Pimentel wrote:
> I recently had to port a script from using Postgres to MySQL.
> In Postgres, when I have to use a unique id, I create a sequence
In Postgres, you can use "serial" column type. In MySQL, it is
"auto_increment".
Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.
^ permalink raw reply [nested|flat] 27+ messages in thread
* RE: [SQL] auto_increment?
@ 1999-10-21 16:31 Pham, Thinh <tpham@mail.priority.net>
0 siblings, 0 replies; 27+ messages in thread
From: Pham, Thinh @ 1999-10-21 16:31 UTC (permalink / raw)
To: 'Rodrigo Bernardo Pimentel' <rbp@pobox.com>; pgsql-sql
How about using "serial" in the place of "auto_increment"? such as
CREATE TABLE table (
id serial,
name text,
email text);
I guess you already know this but i thought i'd just mention it anyway,
using "table" for table name wouldn't work ;-)
Thinh
> -----Original Message-----
> From: Rodrigo Bernardo Pimentel [mailto:rbp@pobox.com]
> Sent: Thursday, October 21, 1999 10:45 AM
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] auto_increment?
>
>
> Hi.
> I recently had to port a script from using Postgres to MySQL.
> In Postgres, when I have to use a unique id, I create a sequence
> and call nextval('sequence') each time I insert a new line in
> the table.
> MySQL doesn't have sequences (as far as the
> documentation shows).
> So I had to look for an alternative. I found an atribute
> "auto_increment"
> which seems pretty interesting. If I do
> CREATE TABLE table (
> id int4 primary key auto_increment,
> name text,
> email text);
>
> It automatically assigns the next value when I do
> INSERT INTO table (nome, email) values ('My name',
> 'me@somewhere.com');
>
> When I noticed MySQL didn't have sequences, I cursed a
> lot. When I
> found this out, though, I kept wondering if Postgres also had
> it, but I
> couldn't find any mention in the documentation. Does anyone
> know if something
> similar exists? Am I doing thins the hard way? Is there any
> other way of
> automagically getting unique ids?
> TIA,
>
>
> rbp
>
> ______________________________________________________________
> __________
> Rodrigo Bernardo Pimentel <rbp@pobox.com>| GPG KeyID: 81F85A48
> LinuxSP <http://www.linuxsp.org.br;| Fingerprint:
> AirGeeks <http://www.airgeeks.org;|7E62 9CA2 C95B
> FC86 B334
> _____________ Debian Linux User ______________|203E C011 2E4D
> 81F8 5A48
>
> ************
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* AUTO INCREMENT
@ 2000-06-10 10:02 GANESH KUMAR <itsganesh@yahoo.com>
0 siblings, 1 reply; 27+ messages in thread
From: GANESH KUMAR @ 2000-06-10 10:02 UTC (permalink / raw)
To: pgsql-sql
i am searching for
autoincrement in table
in table employee
i want give empno as autoincrement
is there any syntax
like this
create table emp
(empno integer auto_increment);
send reply
ganesh
__________________________________________________
Do You Yahoo!?
Yahoo! Photos -- now, 100 FREE prints!
http://photos.yahoo.com
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: AUTO INCREMENT
@ 2000-06-10 10:05 M.Mazurek@poznan.multinet.pl
parent: GANESH KUMAR <itsganesh@yahoo.com>
0 siblings, 0 replies; 27+ messages in thread
From: M.Mazurek@poznan.multinet.pl @ 2000-06-10 10:05 UTC (permalink / raw)
To: GANESH KUMAR <itsganesh@yahoo.com>; +Cc: pgsql-sql
On Sat, 10 Jun 2000, GANESH KUMAR wrote:
> i am searching for
> autoincrement in table
>
> in table employee
> i want give empno as autoincrement
create table xyz (id serial primary key);
check documentation what it really is:).
Marcin Mazurek
--
administrator
MULTINET SA o/Poznan
http://www.multinet.pl/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: AUTO INCREMENT
@ 2000-06-10 10:34 Lucile Dirry <Luciled@wanadoo.fr>
0 siblings, 0 replies; 27+ messages in thread
From: Lucile Dirry @ 2000-06-10 10:34 UTC (permalink / raw)
To: M.Mazurek@poznan.multinet.pl; GANESH KUMAR <itsganesh@yahoo.com>; +Cc: pgsql-sql
Check manual/user for details on 'sequences'.
Good luck.
-----Message d'origine-----
De : M.Mazurek@poznan.multinet.pl <M.Mazurek@poznan.multinet.pl>
À : GANESH KUMAR <itsganesh@yahoo.com>
Cc : pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Date : samedi 10 juin 2000 12:12
Objet : Re: [SQL] AUTO INCREMENT
>On Sat, 10 Jun 2000, GANESH KUMAR wrote:
>> i am searching for
>> autoincrement in table
>>
>> in table employee
>> i want give empno as autoincrement
>create table xyz (id serial primary key);
>check documentation what it really is:).
>
>
>Marcin Mazurek
>
>--
>administrator
>MULTINET SA o/Poznan
>http://www.multinet.pl/
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Auto increment
@ 2000-08-31 18:22 Mads Jensen <astrup_jensen@hotmail.com>
0 siblings, 2 replies; 27+ messages in thread
From: Mads Jensen @ 2000-08-31 18:22 UTC (permalink / raw)
To: pgsql-sql
Hi
I'm a newbiw with pgsql:
1: Haven't been able to find the officiel manual to pgsql. What's the
complete URL?
2: How can I make an auto increment with PostGreSQL? is it "inherit"?
3: The "SHOW TABLE FROM pcfocus" statement, does it work in pgsql?
If yes, what kind of variable will be "produced" and how should I parse
it PHP?
Is this right:
$sql = "SHOW TABLES FROM mydb";
$test = pg_exec($connect, $sql);
while(list($table) = each($test)){
echo $test."<BR>\n";
?
Thanks alot!
--
Med venlig hilsen/ Wishes
Mads Jensen
Homepage: http://www.pcfocus.f2s.com
E-Mail: astrup_jensen@hotmail.com
--
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Auto increment
@ 2000-09-04 01:24 Richard Rowell <the_pitman@hotmail.com>
parent: Mads Jensen <astrup_jensen@hotmail.com>
1 sibling, 0 replies; 27+ messages in thread
From: Richard Rowell @ 2000-09-04 01:24 UTC (permalink / raw)
To: pgsql-sql
In article <vnxr5.4053$nq.48129@twister.sunsite.auc.dk>, "Mads Jensen"
<astrup_jensen@hotmail.com> wrote:
> Hi
>
> I'm a newbiw with pgsql:
>
> 1: Haven't been able to find the officiel manual to pgsql. What's the
> complete URL?
Hmm, I don't know, I use the book which you can find with all the rest of the docs at:
http://www.postgresql.org/docs/index.html
> 2: How can I make an auto increment with PostGreSQL? is it "inherit"?
One of two ways. Either make the data type "serial" or you can create a sequence and
set the default value of the field to curval(sequence).
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Auto increment
@ 2000-09-06 18:34 Mads Jensen <astrup_jensen@hotmail.com>
parent: Mads Jensen <astrup_jensen@hotmail.com>
1 sibling, 0 replies; 27+ messages in thread
From: Mads Jensen @ 2000-09-06 18:34 UTC (permalink / raw)
To: pgsql-sql
> 1: Haven't been able to find the officiel manual to pgsql. What's the
> complete URL?
> 2: How can I make an auto increment with PostGreSQL? is it "inherit"?
> 3: The "SHOW TABLE FROM pcfocus" statement, does it work in pgsql?
never mind, i found out myself:
1: not officiel, but anyway:
http://hea-www.harvard.edu/MST/simul/software/docs/pkg/pgsql/7.0.1/man/m
an.html
2: UNIQUE
3: Yes!
--
Med venlig hilsen/ Wishes
Mads Jensen
Homepage: http://www.pcfocus.f2s.com
E-Mail: astrup_jensen@hotmail.com
--
^ permalink raw reply [nested|flat] 27+ messages in thread
* Auto Increment
@ 2001-10-22 06:36 Mayuresh Kadu <mayureshk@aftek.com>
0 siblings, 2 replies; 27+ messages in thread
From: Mayuresh Kadu @ 2001-10-22 06:36 UTC (permalink / raw)
To: pgsql-sql
hi all,
could anyone tell me how to make a primary key to AUTO INCREMENT. The
document is not exactly very explainatory about it :)
Thankx in advance ...
Mayuresh
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Auto Increment
@ 2001-10-22 07:30 Arian Prins <prinsarian@zonnet.nl>
parent: Mayuresh Kadu <mayureshk@aftek.com>
1 sibling, 1 reply; 27+ messages in thread
From: prinsarian@zonnet.nl @ 2001-10-22 07:30 UTC (permalink / raw)
To: pgsql-sql
On Mon, 22 Oct 2001 12:06:21 +0530, "Mayuresh Kadu"
<mayureshk@aftek.com> wrote:
>hi all,
>
>could anyone tell me how to make a primary key to AUTO INCREMENT. The
>document is not exactly very explainatory about it :)
>
>Thankx in advance ...
>
>
>
>Mayuresh
>
>
Try first creating a sequence and then using the sequence as a default
value in your table:
-- STEP 1: create sequence
create sequence mytableid_seq;
-- STEP 2: create table
create table mytable (
id int default nextval ('mytableid_seq'),
anyvalue varchar
primary key (id));
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Auto Increment
@ 2001-10-23 15:24 Cory Wright <corz@southcarrollton.net>
parent: Arian Prins <prinsarian@zonnet.nl>
0 siblings, 0 replies; 27+ messages in thread
From: Cory Wright @ 2001-10-23 15:24 UTC (permalink / raw)
To: pgsql-sql
Try using SERIAL:
CREATE TABLE mytable (
MYID SERIAL,
name TEXT
);
It will auto create the sequences for you.
Cory Wright
corz@southcarrollton.net
http://www.southcarrollton.net/
On Mon, 22 Oct 2001, Arian Prins wrote:
> On Mon, 22 Oct 2001 12:06:21 +0530, "Mayuresh Kadu"
> <mayureshk@aftek.com> wrote:
>
> >hi all,
> >
> >could anyone tell me how to make a primary key to AUTO INCREMENT. The
> >document is not exactly very explainatory about it :)
> >
> >Thankx in advance ...
> >
> >
> >
> >Mayuresh
> >
> >
>
> Try first creating a sequence and then using the sequence as a default
> value in your table:
>
>
> -- STEP 1: create sequence
> create sequence mytableid_seq;
>
> -- STEP 2: create table
> create table mytable (
> id int default nextval ('mytableid_seq'),
> anyvalue varchar
> primary key (id));
>
> ---------------------------(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
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Auto Increment
@ 2001-10-23 17:24 Joe Barrero <jbarrero@SweepsClub.com>
0 siblings, 0 replies; 27+ messages in thread
From: Joe Barrero @ 2001-10-23 17:24 UTC (permalink / raw)
To: pgsql-sql
create table mytable (
myfield serial primary key,
myotherfield integer);
Using the SERIAL data type automatically creates the sequence and default
statements for you.
-----Original Message-----
From: Mayuresh Kadu [mailto:mayureshk@aftek.com]
Sent: Monday, October 22, 2001 2:36 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Auto Increment
hi all,
could anyone tell me how to make a primary key to AUTO INCREMENT. The
document is not exactly very explainatory about it :)
Thankx in advance ...
Mayuresh
---------------------------(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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Auto Increment
@ 2001-10-23 18:38 Josh Berkus <josh@agliodbs.com>
parent: Mayuresh Kadu <mayureshk@aftek.com>
1 sibling, 0 replies; 27+ messages in thread
From: Josh Berkus @ 2001-10-23 18:38 UTC (permalink / raw)
To: Mayuresh Kadu <mayureshk@aftek.com>; +Cc: pgsql-sql
Mayuresh,
First, beginner's questions belong on the PGSQL-NOVICE list.
> could anyone tell me how to make a primary key to AUTO INCREMENT. The
> document is not exactly very explainatory about it :)
No, it is quite clear. See:
http://www.postgresql.org/idocs/index.php?datatype.html
section 3.1.1
I can't imagine how it would be more clear.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 27+ messages in thread
* auto_increment
@ 2003-09-20 05:10 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 2 replies; 27+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-09-20 05:10 UTC (permalink / raw)
To: pgsql-sql
How to Create auto_increment field in PostreSQL.
Can I create them using Trigger.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 06:23 Oliver Elphick <olly@lfix.co.uk>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
1 sibling, 2 replies; 27+ messages in thread
From: Oliver Elphick @ 2003-09-20 06:23 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: pgsql-sql
On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> How to Create auto_increment field in PostreSQL.
> Can I create them using Trigger.
Use the SERIAL datatype. See also the functions nextval(), currval()
and setval().
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But my God shall supply all your need according to his
riches in glory by Christ Jesus." Philippians 4:19
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 07:45 btober@seaworthysys.com
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
1 sibling, 0 replies; 27+ messages in thread
From: btober@seaworthysys.com @ 2003-09-20 07:45 UTC (permalink / raw)
To: middink@indo.net.id; +Cc: pgsql-sql
> How to Create auto_increment field in PostreSQL.
Its called the SERIAL datatype:
create table test_table (
field1 serial,
constraint test_table_pkey primary key (field1));
> Can I create them using Trigger.
Yes, alternatively, but I'm told that is not recommended because the
server source code that manages the special sequence table is highly
optimized for this specific functionality.
~Berend Tober
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 08:43 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 3 replies; 27+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-09-20 08:43 UTC (permalink / raw)
To: pgsql-sql
Ok, but if i do rollback, the auto_increment don't roolback.
How to use nextval(), currval() and setval() functions.
----- Original Message -----
From: Cavit Keskin
To: 'Muhyiddin A.M Hayat'
Sent: Saturday, September 20, 2003 2:15 PM
Subject: RE: [SQL] auto_increment
Create table tablename(
id serial,
....
);
Serial type is integer and autoincrement
When you create this table creates automatic sequence tablename_id_seq
Stored sequence last value;
Try execSQL : select * from tablename_id_seq;
My english is very very little and bad
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 10:05 Richard Huxton <dev@archonet.com>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
2 siblings, 1 reply; 27+ messages in thread
From: Richard Huxton @ 2003-09-20 10:05 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; pgsql-sql
On Saturday 20 September 2003 09:43, Muhyiddin A.M Hayat wrote:
> Ok, but if i do rollback, the auto_increment don't roolback.
It's not supposed to.
> How to use nextval(), currval() and setval() functions.
Something like:
INSERT INTO my_table(nextval('my_sequence_name'),'aaa',1);
But you'll get the same problem.
What are you trying to do with the auto-increment? If you want to guarantee
that the numbers go up in sequence and have no gaps (e.g. 1,2,3,4,5 NOT
1,2,4,6) then you'll need to do some more work.
Think about what you want to have happen when three clients insert rows at the
same time and one rolls back. Once you've decided what you want, ask again if
you need some help.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 10:14 Muhyiddin A.M Hayat <middink@indo.net.id>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 27+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-09-20 10:14 UTC (permalink / raw)
To: pgsql-sql
Where/How can i put this below sql statement, to set value of
guest_guest_id_seq before i do insert to table
SELECT setval('guest_guest_id_seq', max(guest.guest_id)) FROM guest;
i have been try
CREATE TRIGGER "before_insert_guest_update_room_number" BEFORE INSERT
ON "public"."guest" FOR EACH ROW
EXECUTE PROCEDURE "public"."generate_guest_id"();
but error
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>; <pgsql-sql@postgresql.org>
Sent: Saturday, September 20, 2003 6:05 PM
Subject: Re: [SQL] auto_increment
> On Saturday 20 September 2003 09:43, Muhyiddin A.M Hayat wrote:
> > Ok, but if i do rollback, the auto_increment don't roolback.
>
> It's not supposed to.
>
> > How to use nextval(), currval() and setval() functions.
>
> Something like:
> INSERT INTO my_table(nextval('my_sequence_name'),'aaa',1);
> But you'll get the same problem.
>
> What are you trying to do with the auto-increment? If you want to
guarantee
> that the numbers go up in sequence and have no gaps (e.g. 1,2,3,4,5 NOT
> 1,2,4,6) then you'll need to do some more work.
>
> Think about what you want to have happen when three clients insert rows at
the
> same time and one rolls back. Once you've decided what you want, ask again
if
> you need some help.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 11:15 Martin Marques <martin@bugs.unl.edu.ar>
parent: Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 27+ messages in thread
From: Martin Marques @ 2003-09-20 11:15 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: pgsql-sql
El Sáb 20 Sep 2003 03:23, Oliver Elphick escribió:
> On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> > How to Create auto_increment field in PostreSQL.
> > Can I create them using Trigger.
>
> Use the SERIAL datatype. See also the functions nextval(), currval()
> and setval().
Also to add, the auto increment is done through sequences.
I would suggest seeing the docs on SEQUENCE, and SERIAL data type.
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 11:18 Martin Marques <martin@bugs.unl.edu.ar>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
2 siblings, 0 replies; 27+ messages in thread
From: Martin Marques @ 2003-09-20 11:18 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; pgsql-sql
Why do you want it to rollback?
El Sáb 20 Sep 2003 05:43, Muhyiddin A.M Hayat escribió:
> Ok, but if i do rollback, the auto_increment don't roolback.
> How to use nextval(), currval() and setval() functions.
>
> ----- Original Message -----
> From: Cavit Keskin
> To: 'Muhyiddin A.M Hayat'
> Sent: Saturday, September 20, 2003 2:15 PM
> Subject: RE: [SQL] auto_increment
>
>
> Create table tablename(
>
> id serial,
>
> ....
>
> );
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 11:55 Richard Huxton <dev@archonet.com>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 0 replies; 27+ messages in thread
From: Richard Huxton @ 2003-09-20 11:55 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; pgsql-sql
On Saturday 20 September 2003 11:14, Muhyiddin A.M Hayat wrote:
> Where/How can i put this below sql statement, to set value of
> guest_guest_id_seq before i do insert to table
> SELECT setval('guest_guest_id_seq', max(guest.guest_id)) FROM guest;
>
> i have been try
>
> CREATE TRIGGER "before_insert_guest_update_room_number" BEFORE INSERT
> ON "public"."guest" FOR EACH ROW
> EXECUTE PROCEDURE "public"."generate_guest_id"();
>
> but error
The whole idea of sequences is that you don't need to keep altering them.
Usual usage would be something like:
INSERT INTO guest (guest_id, room_number)
VALUES (nextval('guest_guest_id_seq'), 123);
Or, if you have defined guest_id as a SERIAL (which just sets DEFAULT to the
nextval() call for you).
INSERT INTO guest (guest_id, room_number)
VALUES (DEFAULT, 123);
or
INSERT INTO guest (room_number)
VALUES (123);
So long as you always use the sequence, then guest_id will get a different
number each time.
If you already have some entries in guest, and create the sequence later, then
before you start you'll want to call setval(), but you'll only need to do
this once, to skip the numbers you have already used.
Does that make it clearer?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-20 14:00 Christopher Browne <cbbrowne@acm.org>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
2 siblings, 0 replies; 27+ messages in thread
From: Christopher Browne @ 2003-09-20 14:00 UTC (permalink / raw)
To: pgsql-sql
In the last exciting episode, middink@indo.net.id ("Muhyiddin A.M Hayat") wrote:
> Ok, but if i do rollback, the auto_increment don't roolback.
Right, it's not supposed to.
Think about the situation where you have 5 clients connecting to the
database and adding records to this table.
The current functionality of sequences means that with a little
cacheing of values, they can all be hammering the table with inserts
and never need to worry about what the other is doing.
If the increment was rolling back by one when an INSERT was rolled
back, that would mean that the cache size was just 1, and access to
that sequence would have to be serialized across all accessors, which
would slow it down incredibly.
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/oses.html
"Let me blow that up a bit more for you."
-- Colin Powell, Discussing a picture of the intelligence compound in
Iraq
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-22 05:15 sad <sad@bankir.ru>
parent: Oliver Elphick <olly@lfix.co.uk>
1 sibling, 1 reply; 27+ messages in thread
From: sad @ 2003-09-22 05:15 UTC (permalink / raw)
To: pgsql-sql
On Saturday 20 September 2003 10:23, you wrote:
> On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> > How to Create auto_increment field in PostreSQL.
> > Can I create them using Trigger.
>
> Use the SERIAL datatype. See also the functions nextval(), currval()
> and setval().
I believe it is better to EXPLICITLY declare a SEQUENCE and
set the default value nextval() to the field.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: auto_increment
@ 2003-09-22 14:22 Bruno Wolff III <bruno@wolff.to>
parent: sad <sad@bankir.ru>
0 siblings, 0 replies; 27+ messages in thread
From: Bruno Wolff III @ 2003-09-22 14:22 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
On Mon, Sep 22, 2003 at 09:15:04 +0400,
sad <sad@bankir.ru> wrote:
> On Saturday 20 September 2003 10:23, you wrote:
> > On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote:
> > > How to Create auto_increment field in PostreSQL.
> > > Can I create them using Trigger.
> >
> > Use the SERIAL datatype. See also the functions nextval(), currval()
> > and setval().
>
> I believe it is better to EXPLICITLY declare a SEQUENCE and
> set the default value nextval() to the field.
The disadvantage of that system is that the dependency isn't detected.
With serial type you won't be able to drop the sequence without
dropping the column and when you drop the table, the sequence will
get dropped as well. (The preceding applies to 7.4. Things may not
be checked as well in 7.3.)
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2003-09-22 14:22 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-21 15:44 auto_increment? Rodrigo Bernardo Pimentel <rbp@pobox.com>
1999-10-21 16:12 ` David Rugge <davidrugge@mindspring.com>
1999-10-21 16:12 ` Oleg Broytmann <phd@phd.russ.ru>
1999-10-21 16:31 RE: [SQL] auto_increment? Pham, Thinh <tpham@mail.priority.net>
2000-06-10 10:02 AUTO INCREMENT GANESH KUMAR <itsganesh@yahoo.com>
2000-06-10 10:05 ` Re: AUTO INCREMENT M.Mazurek@poznan.multinet.pl
2000-06-10 10:34 Re: AUTO INCREMENT Lucile Dirry <Luciled@wanadoo.fr>
2000-08-31 18:22 Auto increment Mads Jensen <astrup_jensen@hotmail.com>
2000-09-04 01:24 ` Re: Auto increment Richard Rowell <the_pitman@hotmail.com>
2000-09-06 18:34 ` Re: Auto increment Mads Jensen <astrup_jensen@hotmail.com>
2001-10-22 06:36 Auto Increment Mayuresh Kadu <mayureshk@aftek.com>
2001-10-22 07:30 ` Re: Auto Increment Arian Prins <prinsarian@zonnet.nl>
2001-10-23 15:24 ` Re: Auto Increment Cory Wright <corz@southcarrollton.net>
2001-10-23 18:38 ` Re: Auto Increment Josh Berkus <josh@agliodbs.com>
2001-10-23 17:24 Re: Auto Increment Joe Barrero <jbarrero@SweepsClub.com>
2003-09-20 05:10 auto_increment Muhyiddin A.M Hayat <middink@indo.net.id>
2003-09-20 06:23 ` Re: auto_increment Oliver Elphick <olly@lfix.co.uk>
2003-09-20 11:15 ` Re: auto_increment Martin Marques <martin@bugs.unl.edu.ar>
2003-09-22 05:15 ` Re: auto_increment sad <sad@bankir.ru>
2003-09-22 14:22 ` Re: auto_increment Bruno Wolff III <bruno@wolff.to>
2003-09-20 07:45 ` Re: auto_increment btober@seaworthysys.com
2003-09-20 08:43 Re: auto_increment Muhyiddin A.M Hayat <middink@indo.net.id>
2003-09-20 10:05 ` Re: auto_increment Richard Huxton <dev@archonet.com>
2003-09-20 10:14 ` Re: auto_increment Muhyiddin A.M Hayat <middink@indo.net.id>
2003-09-20 11:55 ` Re: auto_increment Richard Huxton <dev@archonet.com>
2003-09-20 11:18 ` Re: auto_increment Martin Marques <martin@bugs.unl.edu.ar>
2003-09-20 14:00 ` Re: auto_increment Christopher Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox