current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 1 down vote favorite

I'm having problems comparing Postgres types, and would be grateful for some help. I am extracting valid document types from a configuration table that holds a tilda-separated string, as follows:

SELECT string_to_array(value,'|') as document_kinds
  FROM company_configs
  WHERE option = 'document_kinds'

this gives me an array of values, so

'doc1|doc2|doc3' becomes {doc1,doc2,doc3}

Next I need to select the documents for a given person which match my document types:

SELECT * FROM people
  JOIN documents ON ...
  WHERE kind IN
   (SELECT string_to_array(value,'|') as document_kinds
    FROM company_configs
    WHERE option = 'document_kinds')

the documents.kind column is 'character varying' my understanding is that string_to_array is producing an array of text values 'text[]'

This query produces the error 'ERROR: operator does not exist: character varying = text[]'

If I cast 'kind' into text, with 

SELECT * FROM people
 JOIN documents ON ...
 WHERE kind::text IN
  (SELECT string_to_array(value,'|') as visa_document_kinds FROM languages_united.company_configs WHERE option = 'visa_document_kinds')

I get the error 'ERROR: operator does not exist: text = text[]'

I'm not sure how to compare the two, and would be grateful for any advice.

Thanks in advance Dan

Postgres 9.4.1

share|improve this question
up vote 0 down vote accepted

You can select against any array element by using the ANY operator, if your sub-query returns exactly one row:

SELECT *
FROM people
JOIN documents ON ...
WHERE kind = ANY (
  SELECT string_to_array(value,'|') as document_kinds
  FROM company_configs
  WHERE option = 'document_kinds');

If the sub-query possibly returns multiple rows, you can use the regexp_split_to_table() function:

SELECT *
FROM people
JOIN documents ON ...
JOIN (
  SELECT document_kinds
  FROM company_configs,
       regexp_split_to_table(value, '\|') as document_kinds
  WHERE option = 'document_kinds') sub ON sub.document_kinds = kind;

(You will have to tweak this to match the rest of your query.)

share|improve this answer
    
thanks very much for your response, with a few tweaks that has worked - the regex_split_to_table doesn't accept/require the 'g' option, and the delimiter needed to be escaped to '\|'. Thanks again. Dan – d_a_n Oct 28 '15 at 13:42
    
Ah, ok. The 'g' flag in regex indicates that all occurrences have to be matched but I suppose that that is obvious for this function. Answer updated. – Patrick Oct 28 '15 at 17:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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