Casting text type column to json type in Postgresql

Question

I have column typed text in my database which has json queries in it. I want to cast type of the column to json in postgresql how can I do that?

UPDATE category_query_copy
set json_queries = query_json

my query is like this and the error message:

[Err] ERROR: column "json_queries" is of type json but expression is of type text

LINE 2: set json_queries = query_json ^ HINT: You will need to rewrite or cast the expression.

Answer 1

PostgreSQL is fussy about data types, and won't implicitly convert from text to json even though they seem like they're both textual types.

You must use an explicit cast, e.g.

UPDATE category_query_copy
set json_queries = CAST(query_json AS json)