PSQLException: ERROR: syntax error at or near

Question

I have what I thought was a straight forward relation in JPA. Looks like this. CompanyGroup:

@Entity
@Table
public class CompanyGroup implements Serializable {


    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    private Long id;
    @Column(name = "name")
    private String name;
    @JoinColumn(name = "companies")
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<Company> companies;
}

Company:

@Entity
@Table
public class Company implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(name = "name")
    private String name;
    @JoinColumn(name = "users")
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<User> users;

    @Id
    @GeneratedValue
    private Long id;
}

User:

@Entity
@Table
public class User {

    @Column(name = "firstName")
    private String firstName;
    @Column(name = "lastName")
    private String lastName;
    @Column(name = "email")
    private String email;


    @Id
    @GeneratedValue
    private Long id;
}

I have omitted setters, getters, etc.

This is not working. I'm trying to save a CompanyGroup(Has 2 companies, each company has 2 users, all entities are unique) to a fully empty database.

I persist this using Spring-Data, accessed in a service like this:

@Service
public class ConcreteCompanyGroupService implements CompanyGroupService {

    @Autowired
    private CompanyGroupRepository repository;
    @Transactional
    @Override
    public void save(CompanyGroup group) {
        repository.save(Collections.singleton(group));
    }
}

When I try to call this method I receive this:

 org.postgresql.util.PSQLException: ERROR: syntax error at or near "User"
  Position: 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)

Hopefully I have done something stupid that someone can find quickly. I don't know how to solve this.

EDIT:

The driver in my pom.xml:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4.1211</version>
</dependency>

Answer 1

Your entity maps across to a table name that is an SQL reserved keyword (User). Sadly for you, your chosen JPA provider does not automatically quote the table name identifier, and so you get exceptions when referring to the table.

Solution is either to quote the table name yourself in the @Table annotation, or change the table name to not be a reserved keyword. Alternatively use a JPA provider that auto-quotes such reserved keywords for you (e.g DataNucleus)

Answer 2

Solution 1: As Pascal mentioned, you have to escape the table name with backslash like:

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

Solution 2: Rename your table's anme with another name (Users)

@Entity
@Table(name="Users")
public class User {
    ...
}

Solution 3: Add a suffix to the table's name:

@Entity
@Table(name="APP_User")
public class User {
    ...
}

Solution 4: Change the entity name, e.g. ApplicationUser

@Entity
public class ApplicationUser {
    ...
}

The reason

PostgreSQL as some reserved SQL Key Words. For example: ABORT, ALL, ARRAY, CACHE, CUBE, USER, ... Those tokens are in the SQL standard or specific to PostgreSQL