CODEWITHSUNDEEP
postgresqlsequence
Grim
Grim

Reputation: 1986

Cant find postgres sequence

Hibernate can not find my sequence, the exception is:

Caused by: org.postgresql.util.PSQLException: 
              ERROR: relation "default.menuitem_menuitem_id_seq" does not exist

OK, i try it myself and connect do database:

mydb=# CREATE SEQUENCE "default.menuitem_menuitem_id_seq" INCREMENT BY 1 
               MINVALUE 1 NO MAXVALUE START WITH 1 NO CYCLE;
ERROR:  relation "default.menuitem_menuitem_id_seq" already exists
mydb=# select nextval('default.menuitem_menuitem_id_seq');
ERROR:  relation "default.menuitem_menuitem_id_seq" does not exist
LINE 1: select nextval('default.menuitem_menuitem_id_seq');

Do i have a sequence and what is its name?

Upvotes: 1

Views: 3080

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Explanation

What you did is you actually created a sequence with the name of "default.menuitem_menuitem_id_seq" inside the current schema (probably public).

Information & Approach

default is a reserved keyword both in ANSI SQL Standard and PostgreSQL.

From the Postgres manual:

Key Word    PostgreSQL  SQL:2011    SQL:2008    SQL-92
DEFAULT     reserved    reserved    reserved    reserved

If you want to CREATE SEQUENCE in your schema which can only have a name of "default" then you need to:

CREATE SEQUENCE "default".menuitem_menuitem_id_seq INCREMENT BY 1 
               MINVALUE 1 NO MAXVALUE START WITH 1 NO CYCLE;

Additional quotes around your sequence name aren't really needed here.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

The quoting must be made on each qualifier separetely:

CREATE SEQUENCE "default"."menuitem_menuitem_id_seq" 
INCREMENT BY 1 
MINVALUE 1 NO MAXVALUE 
START WITH 1 
NO CYCLE;

Otherwise a sequence named default.menuitem_menuitem_id_seq will be created in the current schema

Upvotes: 1

Related Questions