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
  • You might be running into this: mesoconcepts.com/2013/06/inconsistent-unnesting-in-postgresql – Denis de Bernardy Dec 18 '14 at 19:31
  • 1
    Can you provide a short demo on sqlfiddle.com so that we can see the raw data? – mu is too short Dec 18 '14 at 20:21
  • 1
    You've used an array of text or varchar, i.e. text[], not an array of integer integer[]. – Craig Ringer Dec 18 '14 at 20:45
  • @CraigRinger so you are suggesting this data was stored improper to begin with, considering they are supposed to be integer ID numbers (which is where they came from to be added to the array) I guess I was hoping there was an easy way to unnest it as a numeric, even it was stored improperly as text – hackg Dec 18 '14 at 21:52
  • 3
    @hackg There is: unnest(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

This worked - thanks @pozs for pointing out my trim was incorrect and fixing that allowed me to cast as integer properly

select study_id, trim(both '''' from unnest(specialties))::integer as spec_id
from studies
where study_id in (2333,2332,2330,2329,2328,2327,2318,2317)

results

    study_id       spec_id  
   (integer)      (integer)  
    2329           1635
    2329           1646
    2327           1696
    2327           1643
    2327           1695

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.