play20 ebean generated sql throws syntax error on

Question

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?

Answer 1

Well, due to my little knowledge about postgresql, I was struggling with this all day. Here's simple solution. Don't use table called "user" on postgreqsl. This table is already used.

But why my evolution sql query worked for initialisation of database? Well if I explicitly specify in which schema I want to create table "user", that basically works.

But if schema is not specified, is used current schema. From documentation:

If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema

So that explains it. But for my project, using "user" model was perfectly reasonable and for H2 file based databased it was working, so I assumed that problem was somewhere else...

Answer 2

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.