Is it possible to get the id to be inserted in the database before insert

Question

I'm using PDO with PostgreSQL in PHP.

I'll let my users uploads files.

I would like to add the record id to the saved image.

Is it possible to get (or reserve) the record id before insertion in the database.

I don't think this is possible just want to be sure.

Answer 1

Yes it is, you should use postgres function nextval to fetch the next value in the sequence. Look the default value in your table's id column, it should contain a nextval call to the used sequence.

For example if the table looks something like this:

image_id bigint not null default nextval('image_image_id_seq'::regclass)
data bytea not null

You can call:

SELECT NEXTVAL('image_image_id_seq') ;

which will "reserve" the id for you to be used later on:

INSERT INTO image (image_id, data) VALUES (?, ?) ;

Answer 2

You can also do this :

BEGIN
INSERT RETURNING
get id
rename file
COMMIT