Search code examples
jsonpostgresqlddlsqldatatypes

Alter column varchar to json in psql

Hi i am trying to convert a column in my table from varchar to json and the table already had some string data. I tried doing that with the below command.

Database=# alter table table_name alter column message type json using
message::json;

But the command failed with the below error.

ERROR:  invalid input syntax for type json
DETAIL:  Token "This" is invalid.
CONTEXT:  JSON data, line 1: This...

Note : The message column has a set of words with spaces like below.

"This is a message"

I am not sure what went wrong. Thanks in advance..

Solution

  • You can use to_jsonb() rather than casting: 

    alter table table_name 
  alter column message type jsonb using to_jsonb(message);

    If you really want to use json (although jsonb is recommended), then cast the result back to a json type:

    alter table table_name 
  alter column message type json using to_jsonb(message)::json;

    But this seems rather strange for a column that doesn't contain "real" json values, only plain strings.