Issues with JSONB columns in Postgres

[wleev]

When inserting or updating JSONB columns errors are being thrown, this seems to be due to JSON/JSONB sharing the same parameter code generator. There is no issue on reading side, only the writing side.

Error when updating a JSONB column:
Npgsql.PostgresException: 42804: column "data" is of type jsonb but expression is of type text

Changing the generated code from ( since it's a nullable column ):
command.Parameters.AddWithValue("@data", args.Data.HasValue ? args.Data.Value.GetRawText() : (object)DBNull.Value);
to
command.Parameters.AddWithValue("@data", args.Data.HasValue ? args.Data.Value : (object)DBNull.Value);
fixes the issue.

I've looked at potential code fixes. The easiest is a simple special clause in GetWriterFn for jsonb columns, but it isn't the prettiest.

Versions:

• sqlc-gen-csharp: v0.21.0
• sqlc: v1.29.0

[SockworkOrange]

can you share your schema and query files, please?

[wleev]

Trimmed down irrelevant columns:

-- Create "sessions" table
CREATE TABLE "sessions" (
  "id" uuid NOT NULL,
  "data" jsonb NULL,
  PRIMARY KEY ("id")
);

-- name: CreateSession :exec
insert into sessions (
    id, data
) values (
    sqlc.arg(session_id), sqlc.narg(data),
);

-- name: UpdateSessionData :exec
update sessions set 
    data = sqlc.arg(data),
where id = sqlc.arg(session_id);

There are no issues with the python and golang generators, but they treat JSONB quite differently.

[SockworkOrange]

@wleev Thanks:)
As a workaround for now, you can add cast to jsonb to your DML statement - see here:

insert into sessions (
id, data
) values (
sqlc.arg(session_id), sqlc.narg(data)::jsonb,
);

However, since you did find a good way to support it in the generated code itself without SQL cast - we will implement and release a fix for this soon. As you said, the solution is in the WriterFn - there is already such setting for that data type, it just requires changing it + modifying the JsonElementTypeHandler in the same manner.

If you want to take the fix you're welcome - just LMK. Otherwise, will get to it in the next week or so probably.

[wleev]

Thanks, that cast works perfectly, even with existing python and golang implementations.

With this fix, I'm good for the time being.

Thank you for the support.

[SockworkOrange]

@wleev fixed in v0.21.1:)