Created
March 25, 2015 22:38
-
-
Save jelder/0bcb4ac2bb06ae4f193d to your computer and use it in GitHub Desktop.
Converting between types of arrays in PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN; | |
CREATE TABLE json_test ( | |
id SERIAL PRIMARY KEY, | |
label text, | |
examples text[] DEFAULT '{}'::text[] | |
); | |
INSERT INTO json_test (label,examples) VALUES ('before','{}'); | |
INSERT INTO json_test (label,examples) VALUES ('before','{"[{\"a\":1}]"}'); | |
SELECT * FROM JSON_TEST; | |
-- This is needed for the conversion to even be attempted by PostgreSQL. | |
ALTER TABLE json_test ALTER examples DROP DEFAULT; | |
-- This doesn't work, though it appears to at first. It results in arrays of strings. | |
-- We might be able to achieve this with a PL/V8 stored procedure as a second step. | |
-- http://baudehlo.com/2014/04/28/postgresql-converting-text-columns-to-json/ | |
ALTER TABLE json_test ALTER examples TYPE json USING to_json(examples); | |
-- Also doesn't work. | |
-- ERROR: cannot cast type text[] to json | |
ALTER TABLE json_test ALTER examples TYPE json USING examples::json; | |
ALTER TABLE json_test ALTER examples SET DEFAULT '[]'; | |
INSERT INTO json_test (label) VALUES ('after'); | |
SELECT * FROM JSON_TEST; | |
SELECT examples->>0->'a' FROM json_test WHERE id = 2; | |
ROLLBACK; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment