Hi Ng,

probably simple question: what is the easiest way to get the id of an
inserted row.
I have the folliwing table:

Create table "testdb"."user"
(
"us_id" Serial NOT NULL UNIQUE,
"us_first_name" Varchar(60),
"us_name" Varchar(60),
"us_email" Varchar(120),
"us_group" Varchar(10),
primary key ("us_id")
);


CREATE SEQUENCE testdb.user_us_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

I now insert data via:

INSERT INTO testdb.\"user\"(us_first_name,us_name,us_email,us_ group)
VALUES('$fname','$name','$email','$group');

Now I'm trying to get the id "us_id" via:

SELECT currval('testdb.user_us_id_seq');


Which doesn't work, I tried several syntax combinations, but no luck.
PostgreSQL version is 7.4

What is the correct syntax/way to do it?

Thanks Stefan

begin Stefan Braumeister schrieb:
Okay, i can reproduce it.

Why do you create this sequence? PG create this automatically.
At this point you got an error:
ERROR: relation "user_us_id_seq" already exists


Thats wrong SQL-syntax.

Works for me, but i havn't created the sequence and with a correct
insert:

test=*# insert into testdb.user (us_first_name) values ('foo');
INSERT 0 1
test=*# SELECT currval('testdb.user_us_id_seq');
currval
---------
1



My guess: you are working with PHP and ignore error-messages and/or you
are inside a transaction and on the first error (sequence already exists)
the transaction failed.



end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Andreas Kretschmer schrieb:
Yeah I don't create that sequence, it was autogenerated.
I just posted the sql code which pgadmin shows for that sequence.

Sorry that's the insert statement from my php code.
Almost right. I just figured it out myself. I tried to use pgadmin to
test my sql statement, of course since I didn't insert a user via
pgadmin it didn't work and I got the error that currval is not valid for
this session.

It works as expected if I call it directly within my php script.

Cheers Stefan