Why does QUOTE_IDENT doubles the embedded quotation mark

Question

The Redshift doc for the QUOTE_IDENT function says

The QUOTE_IDENT function returns the specified string as a string in double quotation marks so that it can be used as an identifier in a SQL statement. Appropriately doubles any embedded double quotation marks.

I understand the first part about surrounding a string with double quotation marks. But when is it appropriate to double embedded quotation marks? Why is that useful?

Answer 1

when is it appropriate to double embedded quotation marks? Why is that useful?

It's always appropriate. It's useful because the output of QUOTE_IDENT is often embedded into a larger string. As such, you then have a quoted string inside the larger string and you detect the end of such a string by finding a single double quote. I.e. assume we pass AB"CD to QUOTE_IDENT. We receive back "AB""CD". We then embed that into:

SELECT "AB""CD" FROM Foo

And when that's parsed later, we find a single identifier AB"CD in the SELECT list.