ERROR: type "datetime" does not exist

Question

I am trying to migrate my hibernate-based app from MySQL to Postgres, but there seems to be a problem with the date format

In my entity I have

@Entity
@Table( name = "APP_USERS" )
public class User {

    @Id
    @GeneratedValue(generator="increment")
    @GenericGenerator(name="increment", strategy = "increment")
    private int id;

    private String username;
    private String email;
    private String password;
    private String firstname;
    private String lastname;
    private Timestamp joindate;
    ...

but I get an error when creating the table

ERROR: type "datetime" does not exist

The sql from "showsql" is

Hibernate: create table APP_USERS (id integer not null, email varchar(255), firstname varchar(255), joindate datetime, lastname varchar(255), password varchar(255), username varchar(255), primary key (id))

I have postgresql-9.5.3-1 installed locally and I am using Hibernate version 5.0.10.Final

Searching for that error on Google gives me very few results.

Answer 1

Your framework needs to generate DDL that uses "timestamp" rather than "datetime".

When any application framework generates the wrong data types for SQL DDL, the most likely problem is that it's configured to use the wrong database. In your case, it's likely still configured to use MySQL. Reconfigure for PostgreSQL.

Answer 2

PostgreSQL doesn't have datetime data type. For a combination of both you need "timestamp without time zone".

https://www.postgresql.org/docs/9.5/static/datatype-datetime.html