postgres comparing varying character with array from string_to_array

Question

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

Answer 1

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.)