开发者

postgresql -- Curval not working, using PHP PDO

So I'm trying to run some SQL here through PHP's PDO (which I don't believe should be the problem) like such:

INSERT INTO example (
    d_id,
    s_id
) 
    VALUES (
    currval('d_id_seq'),
    currval('s_id_seq')
);

I have two sequences called d_id_seq and s_id_sec (lets pretend I have a table named d and a 开发者_运维知识库table named s, and this sequence is a column called ID and serial type).

Now, obviously I'm doing this wrong, as I get an error about the sequence not being used in this session:

Object not in prerequisite state: 7 ERROR: currval of sequence "d_id_seq" is not yet defined in this session

So, how should I write this?


Problem can be solved via the following command:

SELECT last_value FROM d_id_seq;

Note that I'm using PostgreSQL 9.1.9, I do not know about other or older versions.


The error means you did not "use" the sequence in this session (postgres connection). For instance you did not do any INSERTs on the table d.

Perhaps you have a bug in your code and reconnect to postgres after each query ?

A more convenient way to do it is to use INSERT RETURNING on your INSERTs. Then you get the ids.


From the fine manual:

currval
Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

You use currval to get the last value that was pulled out of the sequence in the current session. The usual pattern is to do an INSERT that uses a sequence and then you call currval to figure out what value the INSERT used. If you haven't called nextval with the sequence in question in the current session then there is nothing for currval to return.

Maybe you're actually looking for select max(id) from d and select max(id) from s:

INSERT INTO example (d_id, s_id)
SELECT MAX(d.id), MAX(s.id)
FROM d, s;

Or maybe you need to wrap your d and s inserts in a stored procedure that takes care of inserting in all three tables at once.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜