Cant find postgres sequence

Question

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?

Yes i have a schema called "default"! I need the sequence in a schema. — Grim
– Grim, Commented Feb 21, 2016 at 19:22
Please try creating the sequence without " ". Also, default is a reserved keyword both in ANSI SQL standard and PostgreSQL. See postgresql.org/docs/9.5/static/sql-keywords-appendix.html — Kamil Gosciminski
– Kamil Gosciminski, Commented Feb 21, 2016 at 19:26
@ConsiderMe Thats it, define this as an answer and ill accept it. — Grim
– Grim, Commented Feb 21, 2016 at 19:30

Kamil Gosciminski · Accepted Answer · 2016-02-21 19:44:47Z

1

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.

edited Feb 21, 2016 at 19:44

answered Feb 21, 2016 at 19:35

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2016-02-21 19:37:33Z

1

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

answered Feb 21, 2016 at 19:37

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Cant find postgres sequence

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related