Question 114478 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)
Tags:

3

Answers


  1. 0

    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')")

    Note: This approach of sql query building is not recommended and open to SQL Injection. Better to use PreparedStatement or an ORM framework.

    Login or Signup to reply.
  2. 0

    There is a way to solve this. In the place Where is you specify the jdbc url.

    Ex:

    "jdbc:postgresql://host/schema"

    Change above to

    "jdbc:postgresql://host/schema?stringtype=unspecified"

    Then your db determine type of your params not the jdbc driver.

    Login or Signup to reply.
  3. 0

    The correct solution to this problem is to use a PreparedStatement – do not concatenate parameters into SQL strings.

    Your problem with the date parameter is only the tip of the iceberg.
    The next problem you’ll get is, if Peter O'Donnel signs up.

    So you should use something like this:

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

String insert = "Insert into autore(nome_autore, cognome_autore, datanascita) values(?,?,?)";

PreparedStatement pstmt = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, authorName);
pstmt.setString(2, authorSurname);
pstmt.setObject(3, date, java.sql.Types.DATE);

pstmt.executeUpdate();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search