PostgreSQL next value of the sequences?

Question

I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id of the content. I am able to do this using grocery crud's callback_after_upload function.

I want a next id of the content while adding a new content. I tried to use nextval() function, but sequence gets incremented with it. How can get the last value of the sequence without using nextval() function?

Or is there a simple way I can do this?

Igor

@saji89: Actually, this is outdated and needlessly inefficient, too. You can do this with one round-trip to the server. I added an answer. – Padre

@ErwinBrandstetter It's still worth mentioning that, and mu's demo of a separate nextval call that's used in an insert, because unlike INSERT ... RETURNING both will work via query generation engines and other painful things that may not understand the RETURNING extension :S – Blackstone

@CraigRinger: If your query generator doesn't understand the RETURNING clause (which has been around for years now!), it may be outdated as well - or just a very poor crutch that's looking for replacement. Still, it's good to have the fallback solution a_horse provided. No argument there. – Padre

Answer 1

RETURNING

With a serial or IDENTITY column, you can return the generated ID from the same command:

INSERT INTO tbl(filename)
VALUES ('my_filename')
RETURNING tbl_id;

See:

• Auto increment table column
• Basics in the manual.

Explicitly fetch value

If filename needs to include tbl_id (redundantly), you can still use a single query.

Use lastval() or the more specific currval():

INSERT INTO tbl (filename)
VALUES ('my_filename' || currval('tbl_tbl_id_seq')   -- or lastval()
RETURNING tbl_id;

See:

• Reference value of serial column in another column during same INSERT

If multiple sequences may be advanced in the process (even by way of triggers or other side effects) the sure way is to use currval('tbl_tbl_id_seq').

pg_get_serial_sequence() gets the sequence name

If you don't know the sequence name:

INSERT INTO tbl (filename)
VALUES ('my_filename' || currval(pg_get_serial_sequence('tbl', 'tbl_id'))
RETURNING tbl_id;

'tbl_tbl_id_seq' is the default name for a table 'tbl' with a serial column 'tbl_id'. But there are no guarantees. A column default can fetch values from any sequence if so defined. Also, if the default name is taken when creating the table, Postgres picks the next free name according to a simple algorithm.

'tbl_tbl_id_seq' must be visible in the the current search_path, else you must schema-qualify.

fiddle

Padre

But it doesn't help if you the generated value should be incorporated into the values being inserted. My understanding of "so that I can dynamically name an uploaded file" is that i_nomad wants put the generated ID into one of the values (filename) that is being inserted into the table. But the question is so terribly unclear that it's hard to say. – Suetonius

@a_horse_with_no_name: tbl_id is represented in the serial column anyway. But if you'd want to include it in the filename, you can do that, too. Added a bit to my answer. – Padre

Answer 2

The previously obtained value of a sequence is accessed with the currval() function.

But that will only return a value if nextval() has been called before that.

There is absolutely no way of "peeking" at the next value of a sequence without actually obtaining it.

But your question is unclear. If you call nextval() before doing the insert, you can use that value in the insert. Or even better, use currval() in your insert statement:

select nextval('my_sequence') ...

... do some stuff with the obtained value

insert into my_table(id, filename)
values (currval('my_sequence'), 'some_valid_filename');

Suetonius

I am using grocery crud for insert delete edit operations. I am using 'callback_after_upload' function for renaming a file. I am not passing id for insert. – Igor

@i_nomad: Where did this grocery thing come from suddenly? You asked a question about using sequences in PostgreSQL. – Suetonius

Yes, it is about using sequences in PostgreSQL and I was just explaining what I am trying to do with the 'nextval' of sequence. – Igor

Answer 3

I stumbled upon this question b/c I was trying to find the next sequence value by table. This didn't answer my question however this is how its done, and it may help those looking for the sequence value not by name but by table:

SELECT nextval(pg_get_serial_sequence('<your_table>', 'id')) AS new_id; 

Hope it helps :)

Henka

Answer 4

Answer for 2022

You can use pg_sequence_last_value() if you know the name of the sequence:

SELECT pg_sequence_last_value('public.person_id_seq');

Obsolescent

That's not a PostgreSQL function. – Suetonius

postgresql.org/message-id/flat/… @a_horse_with_no_name it is.... – Booby

hi. the link is broken. – Booby

It's a Postgres function, but not documented (as of pg 16). In this state, it may go away in the next major release without further notice, though very unlikely I'd say. – Padre

Answer 5

If your are not in a session you can just nextval('you_sequence_name') and it's just fine.

Chaff

Answer 6

To answer your question literally, here's how to get the next value of a sequence without incrementing it:

SELECT
 CASE WHEN is_called THEN
   last_value + 1
 ELSE
   last_value
 END
FROM sequence_name

Obviously, it is not a good idea to use this code in practice. There is no guarantee that the next row will really have this ID. However, for debugging purposes it might be interesting to know the value of a sequence without incrementing it, and this is how you can do it.

Spector

Answer 7

I tried this and it works perfectly

@Entity
public class Shipwreck {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
  @Basic(optional = false)
  @SequenceGenerator(name = "seq", sequenceName = "shipwreck_seq", allocationSize = 1)
  Long id;

....

CREATE SEQUENCE public.shipwreck_seq
    INCREMENT 1
    START 110
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

Segregationist

Answer 8

Even if this can somehow be done it is a terrible idea since it would be possible to get a sequence that then gets used by another record!

A much better idea is to save the record and then retrieve the sequence afterwards.

Rab

That's not true. See postgresql.org/docs/8.1/static/functions-sequence.html – Zandrazandt