Insert jsonb into postgresql

Question

I want to create an object and save it ionto the DB

Log l = new Log();

l.setTimestamp("creation_date", Util.getCurrentTimestamp());
l.setString("app_name", "name");
l.setString("log_type", "type");
l.setLong("user_id", 9l);
l.setLong("module_element_id", 9);
l.set("info", JsonHelper.toJsonString("{}"));
l.save();

I've tried mutiple silution but always get this error:

ERROR: column "info" is of type jsonb but expression is of type character varying

How to insert a jsonb?

edit (DDL):

-- Table: public.log

-- DROP TABLE public.log;

CREATE TABLE public.log
(
  id bigint NOT NULL DEFAULT nextval('log_id_seq'::regclass),
  creation_date timestamp without time zone,
  app_name text,
  log_type text,
  user_id bigint,
  module_element_type bigint,
  info jsonb,
  CONSTRAINT log_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.log
  OWNER TO postgres;

Rony, I think this functionality needs to be added to the PostgreSQL dialect and related to: https://github.com/javalite/activejdbc/issues/640. I was trying to find Postgres documentation where it lists all the types that have this strange syntax, but was unable tofind it. If you find, it I will use to add this feature to the framework. — ipolevoy, Oct 10 '17 at 18:01

ipolevoy · Accepted Answer · 2022-01-04T22:38:03.010

This implementation is missing in the framework currently. It was already reported in https://github.com/javalite/activejdbc/issues/640. If you can help tracking down Postgres documentation for all the Postgres data types where this syntax is used with PreparedStatement:

UPDATE my_table SET status = ?::status_type WHERE id = ?

I will then be in a position to quickly implement it for the PostgreSQL dialect: https://github.com/javalite/activejdbc/blob/master/activejdbc/src/main/java/org/javalite/activejdbc/dialects/PostgreSQLDialect.java

Update Jan 4 2022:

The implementation was added to the SNAPSHOT-3.0 and will make it to a next release 3.0 for Java 16.

You can see the discussion here: https://github.com/javalite/javalite/issues/640

Insert jsonb into postgresql

1 Answers1