Inserting image to BYTES column – type “binary” does not exist

I’m trying to insert an image from Spring’s MultipartFile field into Postgres’ BYTEA column with jOOQ but I keep getting confusing error message. What does it mean and how the insert should be done?

CREATE TABLE image
(
    id      SERIAL    PRIMARY KEY,
    data    BYTEA     NOT NULL
)

import org.springframework.web.multipart.MultipartFile;

public class AddIMageForm {
    private MultipartFile image;
}

import javax.persistence.Column;

public class Image {

    @Column(name = "id")
    private Integer id;

    @Column(name = "data")
    private byte[] data;
}

import static com.test.Tables.IMAGE;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.jooq.DSLContext;

@Controller
public class AddImageController {

  @Autowired
  DSLContext jooq;

  @RequestMapping(value = "/addImage", method = RequestMethod.POST)
  public ModelAndView addImagePost(Model model, 
              @ModelAttribute("addImageForm") AddImageForm addImageForm) {

    byte[] imageBytes = addImageForm.getImage().getBytes();
    jooq.insertInto(IMAGE).columns(IMAGE.DATA).values(imageBytes).execute();
    return new ModelAndView("/viewImage");
  }
}

And when executed it gives confusing error message:

org.jooq.exception.DataAccessException: 
SQL [insert into "image" ("data") values (cast(? as binary))]; 
ERROR: type "binary" does not exist
  Position: 58
    at org.jooq_3.10.8.H2.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2241)
        ...

Answer

Your exception stack trace shows you the reason. You have configured the SQLDialect.H2 dialect, but ran your query on PostgreSQL. Use the SQLDialect.POSTGRES dialect.