How to insert varchar into postgresql json column

Question

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!

Mike Organek · Accepted Answer · 2020-08-28 01:34:38Z

1

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));

answered Aug 28, 2020 at 1:34

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brits · Accepted Answer · 2020-08-28 01:08:58Z

1

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.

answered Aug 28, 2020 at 1:08

Brits

18.9k3 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to insert varchar into postgresql json column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related