play20 ebean生成的sql在postgresql上抛出语法错误 [英] play20 ebean generated sql throws syntax error on postgresql

问题描述

我试图用postgresql工作我的play20应用程序，这样我就可以使用并随后部署到Heroku。我遵循此回答。

基本上，我建立了与数据库的连接（所以从本地应用程序到Heroku postgresql数据库的连接工作），但我无法用生成的1.sql进化来初始化数据库。但生成的SQL不起作用，因为postgresql使用模式 （无论如何它应该没有模式，但显然我做错了什么或数据库做错了什么）。

 创建表用户（
 id bigint不为空，
 email varchar（255），
性别varchar（1），
约束pk_user主键（id））; 
  

导致

 错误：在用户或其附近出现语法错误
位置：14 [错误：0，SQLSTATE：42601] 
  

我修正了将表格添加到表名的问题

 创建表public.user（
 ... 
）; 
  

好的，一切工作，直到我试图读取或写入数据库。我又遇到了sql语法异常，无法使用数据库。看起来像SQL查询是某种程度上是错误的。

任何可能有问题的建议吗？

解决方案在使用其他数据库开发应用程序而不是开发应用程序时，这是非常常见的错误，但幸运的是，还有一种常见的解决方案。您仍然可以使用 User 模型，但是您必须确保使用更改的名称创建数据库表：

  @Entity 
 @Table（name =users）
 public class User extends Model {
 ... 
} 
  

在大多数情况下，您的控制器和型号的名称切换对您来说都是透明的。只有您必须记住切换的地方是 RawSql 查询。

TW，这是一个好主意，在本地安装相同的数据库进行开发因为大多数流行的数据库之间有很多不同之处，比如其他保留关键字，其他允许的类型，甚至是其他自动递增的id方法，所以查找和修复正确值在本地主机上更容易。

I'm trying to get work my play20 application with postgresql so I can use and later deploy to Heroku. I followed this answer.

Basically, I made connection to database (so connection from local application to Heroku postgresql database worked), but I was not able to initialise database with generated 1.sql evolution. But generated sql was not working because of postgresql is using schema (it should work without schema anyway, but apparently I'm doing something wrong or database is doing something wrong).

create table user (
id                        bigint not null,
email                     varchar(255),
gender                    varchar(1),
constraint pk_user primary key (id));

resulted in

ERROR: syntax error at or near "user"
  Position: 14 [ERROR:0, SQLSTATE:42601]

I fixed that with adding schema to table name

create table public.user(
  ...
);

Ok, everything worked until I tried to read or write to database. I got again sql syntax exception and can't work with database. Seems like sql queries are somehow wrong.

Any suggestions where could be problem?

解决方案

That's very common mistake while developing application with other database than in production, but fortunately there is also common solution. You can still use User model, however you have to make sure that creates database table with changed name:

@Entity
@Table(name = "users")
public class User extends Model {
    ...
}

In most cases in your controllers and models name-switch will be transparent for you. Only place where you have to remember the switch are RawSql queries.

BTW, that's good idea to install locally the same database for developing cause there's a lot of differences between most popular databases, like other reserved keywords, other allowed types, even other auto incrementing methods for id, so finding and fixing proper values is just easier on localhost.

这篇关于play20 ebean生成的sql在postgresql上抛出语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！