[FIXED] org.postgresql.util.PSQLException: ERROR: column is of type date but expression is of type integer: cannot write date into postgres using java

Issue

I’m trying to insert a record inside my table but I cannot insert any values into the Date column.

This is the code I use to make an insert:

Connection connection = DatabaseConnection.getInstance().getConnection();
    ResultSet result = null;
    try
    {
        Statement statement = connection.createStatement();
        statement.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
        result = statement.getGeneratedKeys();
    } catch (SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        return result;
    }

How I call this function:

String authorName = "Paul"
String authorSurname = "Mac"
DateTimeFormatter f = DateTimeFormatter.ofPattern( "yyyy-MM-dd" ) ; 
LocalDate date = LocalDate.parse ( "2017-09-24" , f );

"Insert into autore(nome_autore, cognome_autore, datanascita) values('"+authorName+"', '"+authorSurname+"', "+date+")")

The fullstack trace I get:

org.postgresql.util.PSQLException: ERROR: column "datanascita" is of type date but expression is of type integer
  Suggerimento: You will need to rewrite or cast the expression.
  Posizione: 90
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:1259)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:1240)
    at projectRiferimentiBibliografici/com.ProjectRiferimentiBibliografici.DatabaseConnection.QueryManager.executeUpdateWithResultSet(QueryManager.java:113)
    at projectRiferimentiBibliografici/com.ProjectRiferimentiBibliografici.DAOImplementation.AuthorDaoPostgresql.insertAuthor(AuthorDaoPostgresql.java:136)
    at projectRiferimentiBibliografici/com.ProjectRiferimentiBibliografici.Main.MainCe.main(MainCe.java:43)

Solution

Here you are using direct insert sql statement. As you are appending date object to string it will be converted to date.toString() which might not be expected format in sql.

Below is the insert sql statement:

"Insert into autore(nome_autore, cognome_autore, datanascita)
values('"+authorName+"', '"+authorSurname+"', '2017-09-24')")

Answered By – SaiNageswar S

Answer Checked By – David Marino (Easybugfix Volunteer)

Leave a Reply

(*) Required, Your email will not be published