Thread: Java and Currval
I'm having trouble getting the currval function to work from a Java program (or from Postgres at all). I want to get the value of addressid in the table taddress. The best I seem to be able to come up with is 'SELECT currval(taddress.addressid)'. However this gives me the error 'ERROR: relation "1" does not exist' or whatever the number should be. I've tried lots of variations but can't seem to work it out. What am I doing wrong? Thanks. -Sam
On Mon, May 02, 2005 at 05:30:12PM +0100, Sam Adams wrote: > > I'm having trouble getting the currval function to work from a Java > program (or from Postgres at all). I want to get the value of addressid > in the table taddress. The best I seem to be able to come up with is > 'SELECT currval(taddress.addressid)'. The argument to currval() is a quoted sequence name, so you probably need something like this: SELECT currval('taddress_addressid_seq'); In PostgreSQL 8.0 you can use pg_get_serial_sequence() to get the sequence name from the table and column names: SELECT currval(pg_get_serial_sequence('taddress', 'addressid')); -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi, On 5/2/05, Sam Adams <samadams@myfastmail.com> wrote: > I'm having trouble getting the currval function to work from a Java > program (or from Postgres at all). I want to get the value of addressid > in the table taddress. The best I seem to be able to come up with is > 'SELECT currval(taddress.addressid)'. However this gives me the error > 'ERROR: relation "1" does not exist' or whatever the number should be. > I've tried lots of variations but can't seem to work it out. What am I > doing wrong? You should use the sequence as parameter to currval(), like: => \d products Table "public.products" -[ RECORD 1 ]---------------------------------------------------------- Column | proid Type | integer Modifiers | not null default nextval('public.products_proid_seq'::text) => SELECT currval(products.proid); ERROR: relation "1" does not exist => SELECT currval('public.products_proid_seq'::text); nextval --------- 7 (1 row) Regards.
On Mon, May 02, 2005 at 17:30:12 +0100, Sam Adams <samadams@myfastmail.com> wrote: > I'm having trouble getting the currval function to work from a Java > program (or from Postgres at all). I want to get the value of addressid > in the table taddress. The best I seem to be able to come up with is > 'SELECT currval(taddress.addressid)'. However this gives me the error > 'ERROR: relation "1" does not exist' or whatever the number should be. > I've tried lots of variations but can't seem to work it out. What am I > doing wrong? Thanks. I don't know what JAVA brings to the mix, but the argument to currval is a string. Before version 8 you would do something like: SELECT currval('taddress_addressid_seq') This string isn't guaranteed to work, but would normally be the correct one. In 8.0 there is a function that will return the sequence name. Note that you must have also done a nextval or setval call on that sequence in the current session or the value is undefined and trying to access it will result in an error.