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

User · Answer

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

User · Answer

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   lt your table gt     id    AS new id     Hope it helps

User · Answer

If your are not in a session you can just nextval  you sequence name   and it s just fine

User · Answer

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

User · Answer

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

User · Answer

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    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 lt  fiddle here Old sqlfiddle

User · Answer

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

[codeigniter] PostgreSQL next value of the sequences?

Examples related to codeigniter

Examples related to postgresql

Examples related to sequence

Examples related to postgresql-9.1

Examples related to nextval