Apostrophe and Quotation in PostgreSQL -How to replace Apostrophe?

Question

Which are the roles in a query string of `apostrophe` and `quotation mark` in SQL? How can the `apostrophe` be replaced? Main interest is their specific roles in PostgreSQL. Leaving `apostrophe` out messes SQL query, e.g,: ``` SELECT role FROM "USER_ROLES" WHERE "USER_ROLES".login = admin; ``` Gets a 'column' admin does not exist error, when admin is a value. Writing 'admin' corrects the problem: ``` SELECT role FROM "USER_ROLES" WHERE "USER_ROLES".login = 'admin'; ``` Actual problem is using the query in another context: ``` <module-option name="rolesQuery" value="SELECT 'role', 'Roles' FROM "USER_ROLES" WHERE login='?'"/> ``` As requested, table definition is: ``` CREATE TABLE "USER_ROLES" ( login text NOT NULL, role text, CONSTRAINT "USER_ROLES_pkey" PRIMARY KEY (login ), CONSTRAINT "USER_ROLES_login_fkey" FOREIGN KEY (login) REFERENCES "USER" (login) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) ``` PostgreSQL version 9.1.

Accepted Answer

You can replace single-quotes (`'`) with dollar-quoting in PostgreSQL to avoid their special meaning in a different context. (Not sure, though, whether the dollar-sign has a special meaning, too): ``` ``` The other idea would be to use `'` in place of single-quotes: ``` ```

Apostrophe and Quotation in PostgreSQL -How to replace Apostrophe?

Solution

Problem