This is an “I fucked up so you don’t have to” post, and is here so someone googling as I did will have more luck.
I recently upgraded a database from Postgresql 9.1 to 9.3. In the process I wanted to convert my TEXT columns containing JSON to Postgresql’s JSON data type.
Here’s what I did (DO NOT DO THIS):
ALTER TABLE table1 ALTER COLUMN col1 TYPE JSON USING to_json(col1);
Here’s what I should have done:
ALTER TABLE table1 ALTER COLUMN col1 TYPE JSON USING col1::JSON;
What happens with the former is you get a single string (which is valid JSON) containing your JSON. Effectively a double-encoding bug.