column "x" is of type jsonb but expression is of type character varying

[marcj]

Describe the bug

I have a table

create table model1
(
    id  serial primary key,
    doc  jsonb   default '{"flag": false}'::jsonb not null
);

and I want to insert data

    const con = new Connection({
        host: 'localhost',
        user: 'postgres',
        database: 'postgres',
    });
    await con.connect();
    await con.query('INSERT INTO model1 (doc) VALUES ($1)', {params: [JSON.stringify({ flag: true })]});

I get the following error:

Error: column "doc" is of type jsonb but expression is of type character varying

    at /Users/marc/bude/deepkit-framework/packages/postgres/node_modules/postgresql-client/cjs/protocol/pg-socket.js:229:44
    at Backend.parse (/Users/marc/bude/deepkit-framework/packages/postgres/node_modules/postgresql-client/cjs/protocol/backend.js:51:13)
    at PgSocket._handleData (/Users/marc/bude/deepkit-framework/packages/postgres/node_modules/postgresql-client/cjs/protocol/pg-socket.js:222:23)
    at Socket.<anonymous> (/Users/marc/bude/deepkit-framework/packages/postgres/node_modules/postgresql-client/cjs/protocol/pg-socket.js:201:42)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

(btw, the error stack is wrong. My original call con.query is not in the stack trace)

Expected behavior

Goes through without warning like the pg package, which works like that. I do not expect to pass for each parameter an explicit type, as I have often many parameters and this would destroy UX.

Desktop (please complete the following information):

• OS: macOS

[erayhanoglu]

Hi, Do not stringify the object. Just pass the object in the params.

const doc = { flag: true };
await con.query('INSERT INTO model1 (doc) VALUES ($1)', {params: [doc]});

Or you can define bind parameter explicitly.

const doc = { flag: true };
await con.query('INSERT INTO model1 (doc) VALUES ($1)', {
    params: [new BindParam(DataTypeOIDs.jsonb, doc)]
});

[marcj]

thanks! I do use literal values for jsonb too, so string, number, etc. This works fine with pg so would be lovely if this library could support it as well without having to specify each param manually

[erayhanoglu]

pg uses text format for data transfer which is very slow and cumbersome. postgresql-client uses binary format which is fast and performant. This is why we created this library.

In text format everything is converted to the text than sent to the server; dates, numbers, binary data etc. This is why you can use any value to send as literal. text format is easy to implement and easy to use.

If there is no intense data flow in your application, you can use other libraries which uses text format. If your application needs performance you should use a library which uses binary format.

[marcj]

How is text format slower? Last time I benchmarked, V8 was notoriously slow converting utf-8 text to binary and vice versa. Do you have any benchmarks showing that binary is actually faster for Postgres? From my experience writing very fast binary encoder in JS, it gets slower and slower compared to JSON.parse for example the longer strings are in there. if your message primarily consists of numbers and small strings (<12 characters), binary is faster though.

[erayhanoglu]

You can find some benchmark result in the internet. Here is some links that i found.

https://www.postgresql.org/message-id/[email protected]

In the text protocol, data is first converted to string on the client side, sent to the server, and parsed by the server again. This workflow couses so much cpu time for both client and server side. And also the data size to be transered over the network gets larger.

Text transfer format: [Client] raw data -> stringify -> transfer -> [Server] - parse -> execute -> stringify -> transfer -> [Client] parse

Binary transfer format: [Client] raw data -> transfer -> [Server] - execute -> transfer -> [Client]

[marcj]

Thank you! I'll play around with your client in binary mode and compare it with our pg implementation in our benchmark suite. Would be awesome if your client is much faster, then we could switch our orm postgres adapter to yours.

[erayhanoglu]

It will be very nice to have a benchmark result. Don't forget that text columns (chars, varchars, json, jsonb etc.) will not make a difference, thus they are not binary. Binary data columns (number, date, blob etc) and binary column arrays will make sense. And testing memory usage benchmark would be perfect. Please contact me anytime if you need help.

[erayhanoglu]

Thank you! I'll play around with your client in binary mode and compare it with our pg implementation in our benchmark suite. Would be awesome if your client is much faster, then we could switch our orm postgres adapter to yours.

Hi marcj. I'm curious if you were able to create a bechmark result.

[marcj]

I wrote my own postgres client and had the same performance characteristics that I had with mongo client driver. for fixed size data like numbers, boolean, etc the binary is faster. but as soon as you have strings in your app the game changes. you become substantially slower for medium sized strings since you have big beefy loops of utf8 decoders, or use native calls which has call overhead. this always kills performance for anything binary + utf8 related in Nodejs. this will be resolved with wasm stringref proposal but it will take several years

[erayhanoglu]

This is interesting. Can i see the source?