Postgres not creating tables properly with hibernate?

Question

I've setup my hibernate app to use the following properties:

spring.datasource.url: jdbc:postgresql://localhost:5432/users
spring.datasource.username=james
spring.datasource.password=root
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.hibernate.ddl-auto=create-drop
#spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming_strategy: org.hibernate.cfg.ImprovedNamingStrategy
#spring.jpa.database: MySql
spring.jpa.database =  postgresql
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

I've also created a postgres database users, but when I run my application, it throws all these unsuccessful alter table errors?!

Hibernate: drop table if exists user cascade
2016-06-23 10:46:05.406 ERROR 3364 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop table if exists user cascade
2016-06-23 10:46:05.407 ERROR 3364 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: syntax error at or near "user"
  Position: 22

Hibernate: alter table user_credential drop constraint FK_14ncv1m0gqncrbiagrs4uaqo8
2016-06-23 10:36:36.199 ERROR 3235 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table user_credential drop constraint FK_14ncv1m0gqncrbiagrs4uaqo8
2016-06-23 10:36:36.199 ERROR 3235 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: relation "user_credential" does not exist
Hibernate: alter table user_roles drop constraint FK_5q4rc4fh1on6567qk69uesvyf
2016-06-23 10:36:36.200 ERROR 3235 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table user_roles drop constraint FK_5q4rc4fh1on6567qk69uesvyf
2016-06-23 10:36:36.200 ERROR 3235 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: relation "user_roles" does not exist
Hibernate: alter table user_roles drop constraint FK_g1uebn6mqk9qiaw45vnacmyo2
2016-06-23 10:36:36.200 ERROR 3235 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table user_roles drop constraint FK_g1uebn6mqk9qiaw45vnacmyo2
2016-06-23 10:36:36.200 ERROR 3235 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: relation "user_roles" does not exist

Everything works fine on mysql, it creates the tables without any dramas, I was using the following properties with mysql:

#spring.datasource.url: jdbc:mysql://localhost/users
#spring.datasource.username=root
#spring.datasource.password=root
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.jpa.hibernate.ddl-auto=create-drop
##spring.jpa.hibernate.ddl-auto=update
#spring.jpa.hibernate.naming_strategy: org.hibernate.cfg.ImprovedNamingStrategy
#spring.jpa.database: MySql
#spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
##spring.jpa.database-platform=org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect

Could it be because I'm using a Point data type from vividsolutions?

import com.vividsolutions.jts.geom.Point;
@Column(name = "coords", columnDefinition="Geometry", nullable = true)
private Point location;

Do I have todo anything else to allow hibernate to use postgres? I've setup postgis on my postgres server as well.

I removed the Point declaration, and it still throws the same errors...So that's not the problem.

James111 · Accepted Answer · 2016-06-23 01:12:47Z

33

It turns out you cannot have a table named user in postgres. So simply changing the table name from user to user_entity fixed this.

answered Jun 23, 2016 at 1:12

James111

16k17 gold badges89 silver badges130 bronze badges

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

3 Comments

Kalle Richter Over a year ago

@MatheusAraujo I'd say that's the kind of knowledge which is needed in unintuitive software which is always bad software. Experitise can't be the accumulated time wasted for programmers incapacities of writing software that is less intuitive than possible.

xonya Over a year ago

You can't have columns named user too!

Tim Malich Over a year ago

@KarlRichter at least in 2020 this doesn't seem to be true. This works for me without any issues. CREATE TABLE if not exists mynamespace.user(id text, "user" text ); Nevertheless, in combination with JPA I am getting the same issue as shown in the question

Tim Malich · Accepted Answer · 2020-06-12 21:04:52Z

0

Since the accepted answer doesn't seem to be up to date I would like to propose different solution:

Note: I guess you are using JPA and named your Entity User. Also be aware of the \"

@Entity(name = "\"User\"")
public class User implements Serializable {
...
}

Btw: The same issue appears for entities named "authorization"

Although this works for current postgres / hibernate versions, I would rather suggest to name the entity user_entity or users.

answered Jun 12, 2020 at 21:04

Tim Malich

1,42118 silver badges23 bronze badges

Comments

Luci · Accepted Answer · 2021-09-05 08:01:45Z

0

if you have relational field, just use CascadeType.All:

@ManyToMany(fetch = EAGER, cascade = CascadeType.ALL)
private Collection<Role> roles = new ArrayList<>();

answered Sep 5, 2021 at 8:01

Luci

112 bronze badges

Collectives™ on Stack Overflow

Postgres not creating tables properly with hibernate?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related