Error: Column Is of Type Json but Expression Is of Type Character Varying

Issue

I encountered an error when trying to process an SQL query to the database. The error suggests that the input for the “tracks” column only accepts JSON as valid input, but my input is of type character varying. However, in the corresponding params, I used JSON_OBJECT as the input type for the "tracks" column. I'm not sure what's causing the issue, and I've been advised to try running the query with Prepared Statements turned off.

Resolution

Cause

The error occurs because the tracks column expects a JSON object, but the provided input is a plain string of type character varying. PostgreSQL cannot automatically convert this string into JSON format, leading to the error.

Solution

When working within an application, you can automate the transformation of input data and execute the database operation using a JS Object.

Modify insert query

Update the insert query to read the data using {{this.params.parameter_key}} as shown below:

INSERT INTO album (name, artist, release_date, tracks) 
VALUES ({{this.params.albumName}}, {{this.params.artistName}}, {{this.params.releaseDate}}, {{this.params.jsonTracks}});

Use JS Object

Create a JS Object:

Add a function to transform the input data into JSON.
Call the insert query.
Supply other inputs along with the transformed JSON data as parameters.

An example JS Object can be as follows:

export default {
    transformDataToJson (trackName, trackDescription) {
        return JSON.stringify({
            'trackName': trackName, 
            'trackDescription': trackDescription
        });
    },
    async insertAlbumRecord (albumName, artistName, releaseDate, trackName, trackDescription) {
        const jsonTracks = this.transformDataToJson(trackName, trackDescription);
        await insertAlbum.run({
            albumName,
            artistName,
            releaseDate,
            jsonTracks
        });
    }
}

Bind the JS Object

You can now bind the execution of the insertAlbumRecord function to an event, such as a button's click event, and pass the relevant inputs.

By following these steps, you can resolve issues with inserting data into a JSON column in PostgreSQL.

Updated