Error 'failed to find conversion function from unknown to text' in sql query

Question

I need to write a query to which if I pass some list of strings then it will check in particular table if those are present and return which are not present. I have written following query for that

SELECT temp_table.configuration_key FROM (#PLACEHOLDER) temp_table 
LEFT JOIN configuration c ON (c.configuration_key = temp_table.configuration_key) WHERE c.configuration_key IS NULL;

where #PLACEHOLDER will be formed dynamically like following

1 . if single string is passed #PLACEHOLDER will be

SELECT 'xyz' configuration_key

if more than one string is passed #PLACEHOLDER will be

SELECT 'xyz' configuration_key UNION ALL SELECT 'abc'

and so on.

Here 2nd query works fine but gives following error to 1st query

failed to find conversion function from unknown to text 

configuration table has single column named configuration_key

Can anyone tell me whats wrong here. how to form query if there is only one string passed (i.e form a table with single element). I know I can do it by entire different query. But cant I do this using same type of query?

Answer 1

I ran into a similar error trying to do an in-DB_Write of just one record into the database (Failed to find conversion function from unknown to character-varying). My in-DB_Write tool gets its input directly from an in-DB_Connect tool with a custom query. I'm using the Redshift Bulk load driver for my writes (I don't see a way to configure a specific in-DB_Write tool to use the regular driver). The bulk load driver uses the COPY command to load from S3.

After reading your post, I suspected that the in-DB_Write tool is assuming the first row is a header row and that actual data starts from the 2nd row. So I did a union of the exact same SQL in my in-DB_Connect and it worked.

In summary, this fails:

Select Coalesce(Max(etl_process_control.process_id), 0) + 1 as process_id
, 1 as job_id
, getdate() as process_start_dttm
, to_timestamp('1899-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS') as process_end_dttm
, 'I' as process_status_cd
, 'In-Progress' as process_status_desc
, null as process_error_rsn
, -999 as rows_updated
, -999 as rows_inserted
, -999 as rows_deleted
, -999 as rows_failed
 From etl_process_control

But this works:

Select Coalesce(Max(etl_process_control.process_id), 0) + 1 as process_id
, 1 as job_id
, getdate() as process_start_dttm
, to_timestamp('1899-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS') as process_end_dttm
, 'I' as process_status_cd
, 'In-Progress' as process_status_desc
, null as process_error_rsn
, -999 as rows_updated
, -999 as rows_inserted
, -999 as rows_deleted
, -999 as rows_failed
 From etl_process_control
union
Select Coalesce(Max(etl_process_control.process_id), 0) + 1 as process_id
, 1 as job_id
, getdate() as process_start_dttm
, to_timestamp('1899-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS') as process_end_dttm
, 'I' as process_status_cd
, 'In-Progress' as process_status_desc
, null as process_error_rsn
, -999 as rows_updated
, -999 as rows_inserted
, -999 as rows_deleted
, -999 as rows_failed
 From etl_process_control

Lastly, you'll notice that I'm not using a union ALL, so the above query essentially returns only one row.... but somehow it works. This is not an ideal solution but gets around the error.

Answer 2

I am not sure why this error is occurring with a single row subquery, but you can use explicit typecasting like

SELECT temp_table.configuration_key 
FROM (SELECT 'xyz'::text configuration_key
) temp_table 
LEFT JOIN configuration c ON (c.configuration_key = temp_table.configuration_key) 
WHERE c.configuration_key IS NULL;

SQL Fiddle