postgres, backslash symbol in the table name

olshevskiy87's profile image olshevskiy87 posted 9 years ago in General Permalink
hello.
assume we have the table with name 'a\b'.
to get info about columns heidi prepared query like this:
SELECT DISTINCT a.attname AS column_name, a.attnum, a.atttypid
, FORMAT_TYPE(a.atttypid, a.atttypmod) AS data_type
, CASE a.attnotnull WHEN false THEN 'YES' ELSE 'NO' END AS IS_NULLABLE
, com.description AS column_comment, def.adsrc AS column_default
, NULL AS character_maximum_length 
FROM pg_attribute AS a 
JOIN pg_class AS pgc ON pgc.oid = a.attrelid 
LEFT JOIN pg_description AS com ON (pgc.oid = com.objoid AND a.attnum = com.objsubid) 
LEFT JOIN pg_attrdef AS def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum) 
WHERE   a.attnum > 0   
AND pgc.oid = a.attrelid   
AND pg_table_is_visible(pgc.oid)   
AND NOT a.attisdropped   
AND pgc.relname = 'a\\b'  /* <--- problem place */
ORDER BY a.attnum;

the backslash symbol in the literal string 'a\b' has been escaped, therefore the query above returned no result.
can you do something with it?
thank you.

win 7
heidisql 9.2.0.4961
postgresql 9.3.6
ansgar's profile image ansgar posted 9 years ago Permalink
That escaping logic is derived from the MySQL code in Heidi. How does PostgreSQL expect single quotes and escape characters?
ansgar's profile image ansgar posted 9 years ago Permalink
PostgreSQL documentation says that one needs to escape a backslash using a second backslash. But when I run this:
SELECT 'tab\\le', 'tab\nle'

I get the same strings as the input strings:
tab\\le  tab\nle

Does not look like it should, or?
ansgar's profile image ansgar posted 9 years ago Permalink
Nice Gravatar btw. that was my first Pink Floyd CD...
olshevskiy87's profile image olshevskiy87 posted 9 years ago Permalink
hello!

PostgreSQL documentation says that one needs to escape a backslash using a second backslash



yes, but you also need to add the letter 'E' before the opening single quote, i.e.
SELECT E'tab\\le', E'tab\nle'


csv output will be
tab\le;tab
le


it's strange, but heidi is not provide the proper line break for the symbol '\n' (in the result output grid, of course). nonetheless, he is actually there :)

Nice Gravatar btw. that was my first Pink Floyd CD...


thanks! that's one of my favourite albums :)
Code modification/commit from ansgarbecker, 9 years ago, revision 9.2.0.4970
Prepend 'E' to escaped PostgreSQL strings. See http://www.heidisql.com/forum.php?t=18657
ansgar's profile image ansgar posted 9 years ago Permalink
Please try out r4970. I am now prepending an E to escaped strings in PostgreSQL. Works fine here, but I have a gutt feeling that will fail somewhere or at some time.
olshevskiy87's profile image olshevskiy87 posted 9 years ago Permalink
hi, Ansgar!
yesterday I installed the revision 4970 and it looks good. at least for me :)
ok, if there will be any problem with E-escaping i'll try to tell you about it.
thank you very much!
ansgar's profile image ansgar posted 4 years ago Permalink

I just removed that E prefix and also the duplicated backslash for the next build. See issue #62 for reasons. The above stuff should still work, even with less issues now:

Description

Please login to leave a reply, or register at first.