postgresql sequence problems with lastinsertid and zend framework

Question

I have just started working on a project and I am using zend framework and postgresql (normally use MySQL) however I am hitting a problem when I am trying to get the last inserted id when using the Zend_Db insert command.

When using the function $db->lastinsertid('users', 'userid'); I get the following error message:

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "users_userid_seq" does not exist LINE 1: SELECT CURRVAL('users_userid_seq') ^

I've checked the database and the sequence does exist, and both the table and the sequence is owned by the the same user that is being use to access the application.

I've even tried $db->lastSequenceId('users_userid_seq'); but still get the same error message.

I am not sure if the problem is with postgresql (I think most likely) or with the framework.

Has anyone else had a similar problem to this?

Answer 1

The following code works for me (PostgreSQL & Zf):

$db->insert($this->getTableName(), $data);

$id = $db->lastSequenceId($this->_sequence);

Replace $this->_sequence with the sequence you are using in the database.

Answer 2

I see you found your answer, it was simply a typographical error.

FWIW, I'll offer the following suggestion as another reason for the error you saw:

You need to spell the name of the sequence exactly as it is stored, including matching case if the sequence name is stored as anything other than lower-case.

In other words, if the sequence was created with the spelling "Users_userid_seq," but you queried it as "users_userid_seq," this doesn't match and you'll get the error.

Try listing sequences in the psql tool:

postgres=# \ds

This will show you the sequences defined, with their spelling as they are stored in the database.

Answer 3

Check if the schema of the table "users" is in the "search_path" of the Zend_Db session.

Answer 4

The field "userid" is a primary key in the table "users" ? If it's not, change the userid type.