I need to unnest this array so I can join it with a list of IDs(integer)
CREATE TABLE studies
(
study_id INTEGER,
specialties TEXT[]
)
WITH (
OIDS=FALSE
);
CREATE TABLE specialties
(
specialty_id INTEGER,
specialty_text TEXT[]
)
WITH (
OIDS=FALSE
);
STUDIES DATA
study_id specialties
2333 {}
2332 {}
2329 {'1635','1646'}
2328 {}
2327 {'1643','1695','1696'}
SPECIALTIES DATA
specialty_id specialty_text
1635 Nephrology
1643 General Surgery
1646 Nephrology
1692 Internal Medicine
1695 Neurology
Unnesting the array works fine using this query
select study_id, unnest(specialties) as spec_id
from studies
where study_id in (2333,2332,2330,2329)
Results
study_id spec_id
2329 '1635'
2329 '1646'
2327 '1643'
2327 '1695'
2327 '1696'
I want to then join the below specialties table, to bring in the corresponding specialty text. However I seem to be having issue with the unnested values having single quotes ' wrapped around them.
I tried trim and ltrim
select study_id, trim(both '''' from unnest(specialties)) as spec_id
from studies
where study_id in (2333,2332,2330,2329,2328,2327,2318,2317)
But the results still appear exactly the same, single quotes not removed - any ideas to remove these so i can cast as integer or join with a numeric field?
study_id spec_id
2329 '1635'
2329 '1646'
2329 '850761'
2329 '877725'
2329 '1664'
UPDATE
added table definitions and @CraigRinger I tried casting the array to integer, no luck
CAST ATTEMPT:
select study_id, unnest(specialties::integer) as spec_id
from studies
where study_id in (2333,2332,2330,2329,2328,2327,2318,2317)
RESULT:
ERROR: cannot cast type text[] to integer
SQL state: 42846
text
orvarchar
, i.e.text[]
, not an array of integerinteger[]
. – Craig Ringer Dec 18 '14 at 20:45unnest(mycolumn::integer[])
. But it's better to fix the schema. I'm just guessing that that's the problem though... you should show your table definitions, otherwise all I can do is guess. – Craig Ringer Dec 18 '14 at 22:02