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](https://stackoverflow.com/questions/2224503/creating-field-with-reserved-word-name-with-jpa) — Dilnei Cunha, Sep 08 '17 at 13:59

score 8 · Accepted Answer · edited Feb 26 '20 at 07:43

8

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 '20 at 07:43

Community

1
1

answered Sep 08 '17 at 13:51

Albert Bos

2,012
1
15
26

Mark Rotteveel · Answer 2 · 2020-04-07T07:58:19.893

7

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).

See also Mapping Properties:

hibernate.auto_quote_keyword
true or false (default value)
Specifies whether to automatically quote any names that are deemed keywords.

For Spring Boot, you can set this as spring.jpa.properties.hibernate.auto_quote_keyword=true (thanks Nikita Bosik)

edited Apr 07 '20 at 07:58

answered Sep 08 '17 at 14:26

Mark Rotteveel

100,966
191
140
197

2

For Spring Boot: `spring.jpa.properties.hibernate.auto_quote_keyword=true` – Nikita Bosik Apr 06 '20 at 23:42

score -1 · Answer 3 · answered Sep 08 '17 at 14:10

-1

adding schema field in @Entity would also solve the problem

answered Sep 08 '17 at 14:10

ArslanAnjum

1,674
2
17
31

score -2 · Answer 4 · answered May 19 '20 at 09:30

-2

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 '20 at 09:30

devpro1981

1
1

Using the correct dialect, PostgreSQL, on Spring Boot

4 Answers4