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?

@saji89: Actually, this is outdated and needlessly inefficient, too. You can do this with *one* round-trip to the server. I added an answer. — Erwin Brandstetter, Nov 15 '12 at 09:02
@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 — Craig Ringer, Nov 15 '12 at 09:06
@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. — Erwin Brandstetter, Nov 15 '12 at 09:12

Erwin Brandstetter · Answer 1 · 2021-05-24T03:14:44.313

`RETURNING`

That's possible with a single round-trip to the database:

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

tbl_id would typically be a serial or IDENTITY (Postgres 10 or later) column. More 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').

Name of sequence

The string literal 'tbl_tbl_id_seq' in my example is supposed to be the actual name of the sequence and is cast to regclass, which raises an exception if no sequence of that name can be found in the current search_path.

tbl_tbl_id_seq is the automatically generated default 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. And if the default name is taken when creating the table, Postgres picks the next free name according to a simple algorithm.

If you don't know the name of the sequence for a serial column, use the dedicated function pg_get_serial_sequence(). Can be done on the fly:

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

db<>fiddle here
_{Old sqlfiddle}

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. — , Nov 15 '12 at 09:13
@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. — Erwin Brandstetter, Nov 15 '12 at 09:24

score 37 · Answer 2 · answered Nov 15 '12 at 08:15

37

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');

answered Nov 15 '12 at 08:15

1

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. – i_nomad Nov 15 '12 at 09:09
4

@i_nomad: Where did this grocery thing come from suddenly? You asked a question about using sequences in PostgreSQL. – Nov 15 '12 at 09:11
Yes, it is about using sequences in PostgreSQL and I was just explaining what I am trying to do with the 'nextval' of sequence. – i_nomad Nov 15 '12 at 10:42

score 12 · Answer 3 · answered Nov 04 '19 at 16:06

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 :)

score 7 · Answer 4 · answered Jun 24 '22 at 15:46

7

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');

answered Jun 24 '22 at 15:46

Code on the Rocks

11,488
3
53
61

1

That's not a PostgreSQL function. – Dec 06 '22 at 14:02
https://www.postgresql.org/message-id/flat/CAAaqYe_YXrkpLb2xZ0Vxi9hNnwn6tDeE3XAHw1YN8boBjaNX4Q%40mail.gmail.com#fabb846529213ba918c6d7554c962798 @a_horse_with_no_name it is.... – jian Jun 08 '23 at 09:42
hi. the link is broken. – jian Jun 08 '23 at 09:43

score 5 · Answer 5 · answered Jul 19 '16 at 19:17

5

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

answered Jul 19 '16 at 19:17

Irlan Cidade

151
2
3

score 2 · Answer 6 · answered Aug 06 '17 at 15:15

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.

score 0 · Answer 7 · edited Aug 14 '17 at 14:42

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;

score -2 · Answer 8 · answered Nov 15 '12 at 08:18

-2

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.

answered Nov 15 '12 at 08:18

Brian

402
3
12

That's not true. See https://www.postgresql.org/docs/8.1/static/functions-sequence.html – helvete Oct 27 '16 at 09:37

PostgreSQL next value of the sequences?

8 Answers8

`RETURNING`

Explicitly fetch value

Name of sequence

Answer for 2022

Linked

Related