[codeigniter] PostgreSQL next value of the sequences?

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?

This question is related to codeigniter postgresql sequence postgresql-9.1 nextval

The answer is


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.


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;

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

RETURNING

Since PostgreSQL 8.2, 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:

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


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


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.


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


Examples related to codeigniter

DataTables: Cannot read property 'length' of undefined How to set time zone in codeigniter? php codeigniter count rows CodeIgniter: 404 Page Not Found on Live Server Only variable references should be returned by reference - Codeigniter How to pass a parameter like title, summary and image in a Facebook sharer URL How to JOIN three tables in Codeigniter how to get the base url in javascript How to get id from URL in codeigniter? How to disable PHP Error reporting in CodeIgniter?

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to sequence

How do I create a sequence in MySQL? How can I get all sequences in an Oracle database? Fixing the order of facets in ggplot PostgreSQL next value of the sequences? Generate a sequence of numbers in Python How to retrieve the current value of an oracle sequence without increment it? Sequence Permission in Oracle Oracle SQL: Use sequence in insert with Select Statement get next sequence value from database using hibernate Best way to reset an Oracle sequence to the next value in an existing column?

Examples related to postgresql-9.1

Postgresql SQL: How check boolean field with null and True,False Value? must appear in the GROUP BY clause or be used in an aggregate function postgres, ubuntu how to restart service on startup? get stuck on clustering after instance reboot List tables in a PostgreSQL schema PostgreSQL ERROR: canceling statement due to conflict with recovery How do I convert an integer to string as part of a PostgreSQL query? ALTER TABLE, set null in not null column, PostgreSQL 9.1 Strange PostgreSQL "value too long for type character varying(500)" PostgreSQL next value of the sequences? Change type of varchar field to integer: "cannot be cast automatically to type integer"

Examples related to nextval

PostgreSQL next value of the sequences? get next sequence value from database using hibernate