CODEWITHSUNDEEP
sqlpostgresqldatabase-sequence
Mangu Singh Rajpurohit
Mangu Singh Rajpurohit

Reputation: 11420

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 ?

Upvotes: 3

Views: 349

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247123

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.

Upvotes: 3

Related Questions