CODEWITHSUNDEEP
jsonpostgresqltriggerssql-insert
Yeivicr
Yeivicr

Reputation: 43

How to insert varchar into postgresql json column

I have been struggling in how to insert into a table in PG that has a JSON column... I am extracting a bit column from another table, and now I want to insert that bit to the json column, for example:

INSERT INTO TABLE (JSON) VALUES( '{"IsAllowed":"' || is_allowed::TEXT || '"}'::JSON )

is_allowed represents to the bit from another table... And I get:

"column "XXX" is of type json but expression is of type text"

Does anyone know how can I achieve this?

Thanks!

Upvotes: 0

Views: 294

Answers (2)

Mike Organek
Mike Organek

Reputation: 12484

You should be using jsonb instead of json, but that is not part of your question.

PostgreSQL does not allow bidirectional casting between bit and boolean types. The same goes for bit and int.

Please try this:

insert into table (json) values
 (json_build_object('IsAllowed', is_allowed = 1::bit));

Upvotes: 1

Brits
Brits

Reputation: 18290

Your command:

INSERT INTO TABLE (JSON) VALUES( '{"IsAllowed":"' || is_allowed::TEXT || '"}'::JSON )

is casting '"}' to JSON. Try adding some brackets:

INSERT INTO TABLE (JSON) VALUES( ('{"IsAllowed":"' || is_allowed::TEXT || '"}')::JSON )

Note: I could not replicate the error you are receiving (got "Token ""}" is invalid.") but with the brackets this worked fine.

Upvotes: 1

Related Questions