postgresqlplayframeworksyntax-errorslickplayframework-evolutions

Evolutions is saying my SQL has a syntax error

I am using Evolutions to build a database. My 1.sql is:

# User schema
# --- !Ups
create table user (
  id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  mobile BIGINT NOT NULL,
  email TEXT NOT NULL
)
# --- !Downs
drop table user

When I start running my web app, I get this error message from Evolutions:

We got the following error:

ERROR: syntax error at or near "user" Position: 14 [ERROR:0, SQLSTATE:42601], while trying to run this SQL script:

I'm stumped because as far as I can tell it is syntactically OK.

(Image here)

(My web app is the play-slick3-steps app, but using PostgreSQL as the database instead of MySQL)

Solution

  • user is a reserved word in Postgres. You could either escape it by using quotes ("s):

    create table "user" (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  mobile BIGINT NOT NULL,
  email TEXT NOT NULL
)

    Or, better yet, changing its name to something that isn't a reserved word, like users (in plural) for example.