Ошибка is not a sequence postgresql

I have created sequence in postgres.

postgres=# create sequence my_sequence start 5 minvalue 3 increment 1 cycle;
CREATE SEQUENCE

Now I am trying to query the next value from the sequence.

postgres=# select nextval("my_sequence");
ERROR:  column "my_sequence" does not exist
LINE 1: select nextval("my_sequence");

But it’s giving me error, that sequence doesn’t exists. But, when I use single quote with the sequence_name, then it works fine :-

postgres=# select nextval('my_sequence');
 nextval
---------
       5
(1 row)

But as per difference between single quote and double quote in sql, double quotes can be used with any user defined sql object. so, accordingly my_sequence is also user-defined object. So, why I am not able to access it ?

TL;DR: Use single quotes, like in

SELECT nextval('my_sequence');

The argument to

nextval

is not an identifier, but has type

regclass

:

df nextval
                       List of functions
   Schema   |  Name   | Result data type | Argument data types |  Type  
------------+---------+------------------+---------------------+--------
 pg_catalog | nextval | bigint           | regclass            | normal
(1 row)


regclass

is a convenience type that internally is identical to the unsigned 4-byte object identifier type

oid

, but has a type input function that accepts a table, index or sequence name as input.

So you can call

nextval

with the

name

of the table as parameter, and a string is surrounded by single, not double quotes.

I created a PostgreSQL sequence on a PostgreSQL 10.7 dB called

markush_seq

I read from the seq

select nextval('markush_seq’)` ) 

using a java web service:
When I run the web service on eclipse (using java 1.8.161) or call the sequence direct from SQL developer, it works fine and the sequence increments by 1 each time eg:

http://localhost:8086/wipdbws/read-markush-seq

21767823690

21767823691

21767823692

However when I run the webservice on AWS (which uses java 1.8.252) and read from the seq using:

https://aws-location/wipdbws/read-markush-seq

I get the sequence number returned as eg:

21767823692

21767823702

21767823693

21767823703

21767823694

21767823704

The sequence in AWS appears to be a combination of 2 incrementing sequences, 10 apart.
It’s the same java code, the only thing that has changed is:

1. The location of the webservice

   a. AWS – USWEST

   b. Eclipse — London

2. The java version:

   a. 1.8.161 in London

   b. 1.8.252 in US WEST

The seq details are:

SELECT * FROM information_schema.sequences
where sequence_name='markush_seq';

select * from pg_sequences where sequencename='markush_seq';

Any suggestion appreciated.

Likely due to multiple sessions accessing the sequence and sequence cache settings.

Documentation says:

although multiple sessions are guaranteed to allocate distinct
sequence values, the values might be generated out of sequence when
all the sessions are considered. For example, with a cache setting of
10, session A might reserve values 1..10 and return nextval=1, then
session B might reserve values 11..20 and return nextval=11 before
session A has generated nextval=2. Thus, with a cache setting of one
it is safe to assume that nextval values are generated sequentially;
with a cache setting greater than one you should only assume that the
nextval values are all distinct, not that they are generated purely
sequentially. Also, last_value will reflect the latest value reserved
by any session, whether or not it has yet been returned by nextval.

I have a problem where my SQL sequence has to be above a certain number to fix a unique constraint error. Now I started to write an if-statement that checks for a certain number and if it’s below it should be increased to a certain number. The statement is for Postgres.

I got the separate parts running but the connection over if is throwing an error and I don’t know why.

First for selecting the current number:

SELECT nextval('mySequence')

Then to update the number:

SELECT setval('mySequence', targetNumber, true)

The full statement looks something like this in my tries:

IF (SELECT nextval('mySequence') < targetNumber)
THEN (SELECT setval('mySequence', targetNumber, true))
END IF;

and the error is

ERROR:  syntax error at »IF«

Can someone explain to me what I did wrong there because the error message isn’t giving me much to work with? I would appreciate your help.

Try this:

   SELECT setval('mySequence', targetNumber, true) 
    WHERE (SELECT nextval('mySequence') < targetNumber) is true;

You can use postgres functions if you want to use

IF

statement.
You can try something like this:

CREATE SEQUENCE seq_test_id_seq;
CREATE TABLE seq_test(
    id integer NOT NULL DEFAULT nextval('seq_test_id_seq'),
     name VARCHAR 
);
CREATE OR REPLACE FUNCTION seq_test_function(target_number bigint)
    RETURNS void
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100
    
AS $BODY$
DECLARE
    seq_val INTEGER;
    BEGIN
        SELECT nextval('seq_test_id_seq') INTO seq_val;
        RAISE NOTICE 'NEXT SEQUENCE [%]', seq_val;
        
        IF (seq_val < target_number) THEN 
            SELECT setval('seq_test_id_seq', target_number, true) INTO seq_val;
            RAISE NOTICE 'SEQUENCE VALUE MODIFIED [%]', seq_val;
        END IF;
    END;
$BODY$;

Then call the procedure:

select seq_test_function(10);