Re: Creating a sequence - Mailing 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