sqlpostgresqldatabase-sequence

Why I am not able to refer to my sequence with double quotes in postgres?

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 ?

Solution

  • 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.