CODEWITHSUNDEEP
sqlpostgresql
Peter Wone
Peter Wone

Reputation: 18765

Alternate Postgres syntax for escaping reserved words

In a PGSQL query one might use double-quotes to escape a column or table name that happens to be a reserved word, like so

select "name" from sometable;

This is often combined with C#, and the escaping must itself be escaped.

string sql = "select \"name\" from sometable;";

However, there is another C# trick that I would like to use to allow line breaks in my SQL for legibility, like so:

string sql = @"
select
  foo 
from
  sometable;";

And here we come unglued: you can't use backslashes to escape double-quotes in a string in which backslashes and linebreaks are treated as literals.

Generally I exploit the fact that dot notation makes the escaping unnecessary.

string sql = @"
select
   x.name
from
  sometable as x;";

But what of tables with reserved words for names?

string sql = @"
select
  foo 
from
  user;";

Putting aside my burning desire to thump the person who keeps using reserved words for column and table names, I ask for alternate syntax. I tried the widely used square bracket syntax.

string sql = @"
select
  foo 
from
  [user];";

But PGSQL seems to be unhelpful there.

I did find a workaround: dot notation again.

string sql = @"
select
  foo 
from
  public.user;";

But the question stands: alternate notation?

Upvotes: 0

Views: 1282

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

There is no alternative to double quotes for quoting identifiers. You will have to pay the price for the bad design choice of choosing identifiers that are not standard compliant.

Upvotes: 3

Related Questions