Two names, one sequence

Recently, I had an issue with Postgres at work. A service was dying when we were giving specific names for creating a sequence. According to the logs, we were providing the correct input. However, the error logged made no sense. Below is the gist of what happened.

postgres=# create sequence "MultiCase";
CREATE SEQUENCE

postgres=# \ds
                 List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | MultiCase    | sequence | postgres
(1 row)

postgres=# select nextval('MultiCase') as id;
ERROR:  relation "multicase" does not exist
LINE 1: select nextval('MultiCase') as id;
                       ^

As you can see, I created a sequence named MultiCase, and it got listed as a relation. But when I try to get the next value in the sequence, it errors out saying the relation does not exist!

This is when I learned about case sensitivity in Postgres. Any identifier named using single quotes is internally folded into lowercase. However, if you surround it with double quotes, the case is preserved.

Hence to select the next value in the sequence MultiCase, I must surround it with double quotes to preserve the case.

postgres=# select nextval('"MultiCase"') as id;
 id
----
  1
(1 row)

Bonus

Another thing that I learned while debugging this issue was that Postgres allows Unicode characters in identifier names!

postgres=# create sequence "यह देखो";
CREATE SEQUENCE

postgres=# \ds
                 List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | MultiCase    | sequence | postgres
 public | यह देखो       | sequence | postgres
(1 row)

postgres=# select nextval('"यह देखो"') as id;
 id
----
  1
(1 row)

Just because you can, doesn't mean you must. Otherwise, bear the consequences...

Two names, one sequence

Postgres shockers, that might be blockers

Bonus