How to escape quotes in strings in vertica (vsql)?

8.9k views Asked by At

So I need to insert some values into a vertica database (via vsql), which may contain quotes and all sorts of special characters. But vertica does not seem to understand character escaping. For example:

rpt=> select "asdasda\"asdasdad" from some_table limit 1;
rpt"> ";
ERROR:  syntax error at or near "" from some_table limit 1;
"" at character 26
LINE 1: select "asdasda\"asdasdad" from some_table limit 1;

This is not the insert statement, but you should get the idea.

1

There are 1 answers

0
Ivan P On BEST ANSWER

Well, first off I should have used single quotes. Escape sequences used to work in earlier versions (before 4.0 I believe), but now they are off by default. If you do not want to tweak database config parameters you have two options.

Use E' syntax:

select E'somethin\' here' from v_catalog.dual_p;

Or double the quotes that need to be escaped:

select 'somethin'' here' from v_catalog.dual_p;