Re: Creating a sequence - Mailing list pgsql-jdbc
From | Craig Ringer |
---|---|
Subject | Re: Creating a sequence |
Date | |
Msg-id | 480F4F1C.6010301@postnewspapers.com.au Whole thread Raw |
In response to | Creating a sequence (Chris <cmattmiller@gmail.com>) |
Responses |
Re: Creating a sequence
(Craig Ringer <craig@postnewspapers.com.au>)
|
List | pgsql-jdbc |
Chris wrote: > I have an existing table that I need to get the last record number and > increment it. I've read that I need to create a sequence first, but I'm > not sure how to go about doing that with an existing table. If you could post the output of the "\d employee" command and the "\ds" command from psql that'd help. > empID = s.executeQuery("SELECT nextval(emp_uid) FROM employee"); First: you must quote the sequence name in nextval, so that won't work as written even if a sequence called emp_uid does exist. Second, you do not need and must not use the `FROM employee' clause. The FROM clause will cause nextval to be called once for every employee record, which was probably not what you intended. If there was a sequence named 'emp_uid' defined the correct query to obtain a new value from the sequence would be: SELECT nextval('emp_uid'); Note the quotes, and the lack of any FROM clause. In any case: Is there a sequence emp_uid defined? I'm guessing there isn't based on your message. If there isn't one defined you'll need to create one. I recommend following the naming convention and calling it employee_emp_uid_seq, creating it with something like this (assuming nobody's inserting employees at the moment): -- Take note of the return value of: SELECT max(emp_uid) + 1 FROM employee; -- Define the sequence, where '1010' should be replaced with -- 1 greater than the existing maximum employee number, which -- you just queried for. CREATE SEQUENCE employee_emp_uid_seq START 1010; -- Set the employee table to get ID values from the sequence -- if they are not specified ALTER TABLE employee ALTER COLUMN emp_uid DEFAULT nextval('employee_emp_uid_seq'); Now, you can drop all mention of the employee ID column when inserting (allowing Pg to use the sequence to set an ID automatically) or you can explicitly call nextval('employee_emp_uid_seq') to get the ID and specify it in the INSERT like you're currently doing. > org.postgresql.util.PSQLException: ERROR: could not open relation with OID 5 What part of the code threw that exception? Is it possible that you've been messing with the database structure while your code is running and the JDBC driver is using a cached query plan that refers to database objects that no longer exist? -- Craig Ringer
pgsql-jdbc by date: