Using the correct dialect, PostgreSQL, on Spring Boot

Question

I have an entity called User which leads to a table called user which in PostgreSQL should be surrounded with quotes for it to work. I know I can specify a custom table name, but shouldn't Hibernate do the quotes automatically?

I was told that maybe Hibernate is not using the PostgreSQL dialect. Is that possible when my database is configured like this:

spring.datasource.url = jdbc:postgresql://localhost/database_name
spring.datasource.username = username
spring.datasource.password = password

and if that's the case, how do I make Hibernate use the correct dialect?

I tried:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

and

spring.jpa.database=postgresql

and

spring.jpa.database-platform = org.hibernate.dialect.PostgreSQL94Dialect

and

spring.jpa.database=org.hibernate.dialect.PostgreSQLDialect

having no effect on the error:

Hibernate: insert into user (created_at, last_modified_at, account_id, email, hashed_password_salt, name, over_hashed_password, preferred_name, public_key, started_displaying_sites_at, watched_tutorial_at, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2017-09-08 14:41:40.177  WARN 15764 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2017-09-08 14:41:40.177 ERROR 15764 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "user"
  Position: 13

Possible duplicate of Creating field with reserved word name with JPA — Dilnei Cunha
– Dilnei Cunha, Commented Sep 8, 2017 at 13:59

Community · Accepted Answer · 2020-02-26 07:43:16Z

9

The issue is not the dialect, but the query using the reserved PostgreSQL word user. You can add the quotes through

@Entity(name = "`user`")

Or rename the user table.

edited Feb 26, 2020 at 7:43

CommunityBot

11 silver badge

answered Sep 8, 2017 at 13:51

Albert Bos

2,0721 gold badge18 silver badges29 bronze badges

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

Comments

Mark Rotteveel · Accepted Answer · 2020-04-07 07:58:19Z

9

You can specify the configuration property hibernate.auto_quote_keyword with value true to enable automatic quoting of keywords (assuming those are correctly defined in the dialect and/or JDBC database meta data).

1 Comment

Nikita Bosik Over a year ago

For Spring Boot: spring.jpa.properties.hibernate.auto_quote_keyword=true

ArslanAnjum · Accepted Answer · 2017-09-08 14:10:33Z

-1

adding schema field in @Entity would also solve the problem

answered Sep 8, 2017 at 14:10

ArslanAnjum

1,7343 gold badges18 silver badges34 bronze badges

Comments

devpro1981 · Accepted Answer · 2020-05-19 09:30:58Z

-1

You should put the table name on class mapping in escapes, like this:

@Entity(name = "\"user\"")

Escaping using backticks is a Hibernate specific feature; escaping with double quotes is JPA compliant.

answered May 19, 2020 at 9:30

devpro1981

111 bronze badge

1 Comment

Thorsten Schöning Over a year ago

Upvoting, because while it doesn't answer the question, it's related to the (currently) accepted answer mentioning Hibernate specific backticks. That answer doesn't answer the question as well and mentioning that backticks are Hibernate specific is of use. So no need to downvote here too much.

Collectives™ on Stack Overflow

Using the correct dialect, PostgreSQL, on Spring Boot

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related