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.
精彩评论