Long time MySQL user, six weeks into Postgres here.

I'm converting a PHP script that uses MySQL to use Postgres instead.
To substitue the missing MySQL functionaility with mysql_insert_id()
and auto_increment I am using code like this:

$sql = "SELECT nextval('companies_company_id_seq'::text)";

And then I do my insert with that value.

It works fine but I don't understand why it always gives me a +1 value
of what I'm expecting. For example when I have an empty table it
gives me 2 as the first nextval instead of a 1, or if I have two rows
in there already it gives me a 4. Is this by design or am I missing
something? It's not a problem I just want to know what's going on.

TIA..

--
Greg Donald
http://destiney.com/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Tuesday 14 Sep 2004 12:29 am, Greg Donald wrote:
I new to postgresql too.

Even in MySQL, the auto_increment value for a field in the table is kept
separately. I remember using phpmyadmin to change this value. This counter
will keep increasing no matter whether you table is shrinking or increasing.

Just guessing, that sequences in postgesql are kept as separate counters along
with the table, and will only keep on increasing. Postgresql will never check
how many records are there in the table while returning a vlue for this
sequence. Again, I am just guessing that this si teh behaviour. It doesnt
make sense for the databse to check all teh records to find out the value of
a simple counter.

- Sandip


--
Sandip Bhattacharya * Puroga Technologies * sandip (AT) puroga (DOT) com
Work: http://www.puroga.com * Home: http://www.sandipb.net

PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3

It's a naive, domestic operating system without any breeding, but I
think you'll be amused by its presumption.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, 2004-09-13 at 19:59, Greg Donald wrote:
I get the impression that you probably know this, but, just in case:
every time you use nextval(), the sequence is incremented. If you
insert a record with a null value for the SERIAL field, nextval() will
be used to generate a value; if you use it again to find a value it will
increment again. Once nextval() has been used once, whether by default
or directly, you should then use currval() to get the value just
returned for the sequence. You cannot use currval() until after
nextval() has been used in the same session.

I can't see from what you describe how you are managing to run nextval()
twice, but that must be happening somehow. Perhaps you are using $sql
twice, in which case it will increment the sequence each time ??

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"For my thoughts are not your thoughts, neither are
your ways my ways, saith the LORD. For as the heavens
are higher than the earth, so are my ways higher than
your ways, and my thoughts than your thoughts."
Isaiah 55:8,9


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi,

You don't have to select nextval separately. Just do an INSERT without the id
column (presuming it is declared as SERIAL when created) like:

INSERT INTO companies (company_name, company_whatever ) VALUES ( , );

Auto increment is indeed in a separate table that stores the next number in
the sequence, not the last one used. And yes, this will increase 'forever' no
matter how many rows there are or have been in your table.

If you want to reset or otherwise meddle with the sequence use:


select setval('seq-name', (select max(col) + 1 from table));
BR,

Aarni

On Monday 13 September 2004 22:08, you wrote:
--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com |
Pääsintie 26 | 45100 Kouvola | FINLAND |
www.kymi.com | cfm.kymi.com |
aarni (AT) kymi (DOT) com | info (AT) kymi (DOT) com |
+358-5-3755 035 | +358-50-4910 037
-------------------------------------------------
This is a bugfree broadcast to you from a linux system.

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

* Greg Donald <destiney (AT) gmail (DOT) com> [130904, 13:59]:
I think you should put <nextval(....)> directly into the INSERT
instruction, so that it advances only once per added record.
Have a look at Documentation PostgreSQL 7.4, Ch 9.11, table 9.34.
HTH
Regards, Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

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

Bah,

Just the other way around. Sequence storing the last value used.

dataguard=# SELECT * FROM langs_lang_id_seq;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
-------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
langs_lang_id_seq | 12 | 1 | 9223372036854775807 |
1 | 1 | 0 | f | t
(1 row)



INSERT INTO lang(lang_name, lang_show, default_lang)
VALUES('#lang_name#', '#lang_show#', '#default_lang#')



dataguard=# SELECT * FROM langs_lang_id_seq;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
-------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
langs_lang_id_seq | 13 | 1 | 9223372036854775807 |
1 | 1 | 32 | f | t
(1 row)


Sorry...,

Aarni

On Monday 13 September 2004 22:47, you wrote:
--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com |
Pääsintie 26 | 45100 Kouvola | FINLAND |
www.kymi.com | cfm.kymi.com |
aarni (AT) kymi (DOT) com | info (AT) kymi (DOT) com |
+358-5-3755 035 | +358-50-4910 037
-------------------------------------------------
This is a bugfree broadcast to you from a linux system.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Greg Donald wrote:

operation twice. If the field you
are inserting data into is of type serial, whenever a record/row is
inserted, the nextval is inserted.
eg
CREATE TABLE testSeq (
id serial,
name text
);
INSERT INTO testSeq (name) VALUES ('foo');
testdb=# SELECT * FROM testSeq;
id | name
----+------
1 | foo
See, it automatically increments the value.

then INSERT INTO testSeq (name) VALUES ( 'fooBar');

and then if you re-increment the value yourself:
testdb=# SELECT nextval('public.testseq_id_seq'::text);
nextval
---------
3

and then insert another row:
INSERT INTO testSeq (name) VALUES ('bar');
testdb=# SELECT * FROM testSeq;
id | name
----+--------
1 | foo
2 | fooBar
4 | bar
(3 rows)

bar is now 4, because you manually called nextval().

hth
Ron



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Mon, 13 Sep 2004 21:54:11 +0200, Ennio-Sr <nasr.laili (AT) tin (DOT) it> wrote:
I would but then I don't know how to ensure I return that exact same
id from the insert, like mysql_insert_id() does. That's the whole
reason why I aquire the id first, so I know what it is after the
insert. SELECT max(id) would be off under load I think.

I just didn't understand why it appeared to be +2 and not +1 as a
novice might expect. But now I get it. Thanks to all.

--
Greg Donald
http://destiney.com/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Greg Donald wrote:

value was that you
inserted into the serial column.

eg SELECT currval('companies_company_id_seq'::text)

Ron


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Mon, Sep 13, 2004 at 03:55:41PM -0500, Greg Donald wrote:
Are you aware of CURRVAL()?

CREATE TABLE foo (
id SERIAL PRIMARY KEY,
info TEXT NOT NULL
);

INSERT INTO foo (info) VALUES ('First Item');
INSERT INTO foo (info) VALUES ('Second Item');
SELECT CURRVAL('foo_id_seq');

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match