Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequence does not exist when it does - Postgres/Spring Boot

I am writing a Spring Boot web-app and using a Postgres db to persist my data. I created a table in Postgres using create table user (id bigserial primary key not null, name text not null; and identified its sequence_name by looking at the schema (in this case, it is user_id_seq). Then, in my User entity class in Spring Boot, I added the following:

@Entity
@Table(name = "user")
public class User implements Serializable {

    @Id
    @SequenceGenerator(name = "user_local_seq", sequenceName = "user_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_local_seq")
    private Long id;
...

making sure that the sequenceName matches what I saw earlier. Now when I start my spring boot app, I am able to successfully boot it but I get the following "error" in the trace:

main] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: sequence "user_id_seq" does not exist

I killed the app and started it again and this time, I got:

main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop sequence user_id_seq
main] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: sequence "user_id_seq" does not exist

What does this mean? Am I missing something? Any help/insight is appreciated.

like image 378
ion20 Avatar asked Jul 07 '17 05:07

ion20


People also ask

Does PostgreSQL support sequence?

A sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification. To create a sequence in PostgreSQL, you use the CREATE SEQUENCE statement.

How sequence works in Postgres?

In PostgreSQL, a sequence is a user-defined schema-bound object which creates a sequence of integers depending on the particular requirement. In PostgreSQL sequence, the orders of numbers are important. Such as {5,6,7,8,9,10} and {10,9,8,7,6,5} are completely different sequences.

How do I create a sequence in PostgreSQL Pgadmin 4?

Click the Definition tab to continue. Use the fields in the Definition tab to define the sequence: Use the Increment field to specify which value is added to the current sequence value to create a new value. Provide a value in the Start field to specify the beginning value of the sequence.


2 Answers

Here is insight.

ERROR: sequence "user_id_seq" does not exist

It mean your sequence either not exist in database OR the user doesn't has permission to access it.

Solution:

  1. Check user_id_seq in database by command \ds
  2. Grant access on sequence to specific user.
    GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO user_name;
like image 166
Adrian Hartanto Avatar answered Oct 18 '22 19:10

Adrian Hartanto


Put database-specific escape-characters ( " in postgres) around the name of the sequence-name.

The name of the sequence has been changed by the driver. This sometimes happens because the Driver thinks the db-server is case-insensitive but the db-server is case-sensitive.

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TEACHER")
  @SequenceGenerator(name = "TEACHER",  sequenceName = "\"Teahcer_pkey\"")
  @Column(name = "id", nullable = false)
like image 42
Grim Avatar answered Oct 18 '22 19:10

Grim