PostgreSQL 9.2 - Convert TEXT json string to type json/hstore

I have a TEXT column containing valid JSON string.

CREATE TABLE users(settings TEXT);

INSERT INTO users VALUES ('{"language":"en","gender":"male"}');
INSERT INTO users VALUES ('{"language":"fr","gender":"female"}');
INSERT INTO users VALUES ('{"language":"es","gender":"female"}');
INSERT INTO users VALUES ('{"language":"en","gender":"male"}');

I want to transform some fields into a query-able format.

A REGEXP_REPLACE for each field would do (language field and gender field). But since it's valid JSON, is there way to:

• Convert into JSON type
• Convert into hstore type
• Or any other feasible ways

SQLFiddle: http://sqlfiddle.com/#!12/54823

Solution 1:

SELECT cast(settings AS json) from users;

EDIT 7 years later

I highly suggest that you don't use unstructured columns unless your data is unstructured. RDBMS go a very long way. We built a fairly large platform and used user settings as a json column, and it endedup becoming a junk drawer which needed to be cleaned up many years later

Solution 2:

Or in a shortest way than Reza:

SELECT settings::json FROM users;

Then, for selecting language for instance:

SELECT settings::json->>'language' FROM users;

More details on the official documentation.

Solution 3:

Here is a solution from Postgresql: Converting TEXT columns to JSON:

ALTER TABLE table1 ALTER COLUMN col1 TYPE JSON USING col1::JSON;