postgres dblink escape single quote

Question

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 port=1234 dbname=theDBName user=theUser password=thePassword',
    E'SELECT field_1, 
    CASE WHEN field_2 IS NOT NULL 
    THEN \'inactive\' ELSE \'active\' 
    END AS field_status 
    FROM the_table 
     ') 
AS linkresults(field_1 varchar(20),field_2 varchar(8))

If I use double quotes, remove the backslash escape for the single quotes and remove the E before the SELECT statement

SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
    "SELECT field_1, 
    CASE WHEN field_2 IS NOT NULL 
    THEN 'inactive' ELSE 'active' 
    END AS field_status 
    FROM the_table 
     ") 
AS linkresults(field_1 varchar(20),field_2 varchar(8))

I get this:

NOTICE:  identifier "SELECT ..." will be truncated

And the I also get the ERROR as my query has been truncated.

I have escaped with dblink like this before, so is there a server setting or something I need to configure?

I know the query works just fine if I run it on the sql server itself, but not with dblink. Any thoughts?

Postgres version 8.4

I have updated it to something more simple. If it doesn't work I can report the previous answer. — niktrs, Jul 07 '11 at 19:28

score 34 · Accepted Answer · edited May 07 '18 at 05:55

34

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, 
        CASE WHEN field_2 IS NOT NULL 
        THEN ''inactive'' ELSE ''active'' 
        END AS 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, 
        CASE WHEN field_2 IS NOT NULL 
        THEN E\'inactive\' ELSE E\'active\' 
        END AS field_status 
        FROM the_table 
         ') 

AS linkresults(field_1 varchar(20),field_2 varchar(8))

edited May 07 '18 at 05:55

answered Jul 07 '11 at 19:14

niktrs

9,858
1
30
30

Tell me if alternative also works. Would like to know since I don't have postgre installed in my pc. – niktrs Jul 07 '11 at 19:31
E\'inactive\' this worked for me as well your other solution using the two single quotes '' – Phill Pafford Jul 07 '11 at 19:34
You can also use $$ style quotes. – Scott Marlowe Jul 08 '11 at 21:53

score 6 · Answer 2 · edited Apr 17 '13 at 06:41

6

Try this query:

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

edited Apr 17 '13 at 06:41

Nazik

8,696
27
77
123

answered Apr 17 '13 at 06:19

kaushik

131
1
5

1

Giving a bit more context and using a code block would improve this answer. – Jordan Dea-Mattson Apr 17 '13 at 06:38

postgres dblink escape single quote

2 Answers2

Linked

Related