Postgres Dblink Escape Single Quote

Related Link: String literals and escape characters in postgresql Here is my error: ERROR: type 'e' does not exist Here is my query: SELECT * FROM dblink('host=theHostName por

Solution 1:

Try replacing \'inactive\' with ''inactive'' -- caution: two single quotes

SELECT *
    FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword','SELECT field_1, CASEWHEN field_2 ISNOT NULL 
        THEN''inactive'' ELSE ''active'' ENDAS field_status 
        FROM the_table 
         ') AS linkresults(field_1 varchar(20),field_2 varchar(8))

Alternative (previous) solution

SELECT *
    FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword','SELECT field_1, CASEWHEN field_2 ISNOT NULL 
        THEN E\'inactive\' ELSE E\'active\' ENDAS field_status 
        FROM the_table 
         ') AS linkresults(field_1 varchar(20),field_2 varchar(8))

Solution 2:

Try this query:

SELECT *
 FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword','SELECT field_1, CASEWHEN field_2 ISNOT NULL 
 THEN $$inactive$$ ELSE $$active$$ 
 ENDAS field_status 
 FROM the_table') AS linkresults(field_1 varchar(20),field_2 varchar(8))