PostgreSQL - Escape single quote in an column alias

Question

How to escape single quote in an column alias in PostgreSQL?

I tried several syntaxes without success:

SELECT 'Test 1' AS 'O''Connell';

SELECT 'Test 2' AS 'O\'Connell';

SELECT 'Test 3' AS "O''Connell";

PgAdmin screenshot

Identifiers are double quoted so: `SELECT "Test 3" AS "O''Connell" FROM ...;` See [Identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) — Adrian Klaver, Jun 15 '21 at 14:24
@AdrianKlaver "test 3" would be a column, not a text, and the column name would contain 2 single quotes... — JGH, Jun 15 '21 at 14:30
@JGH, if you paid attention to the question you would see that GeoGyro is asking about a column alias and not a string alias and my suggestion is correct for that application. — Adrian Klaver, Jun 15 '21 at 14:39

score 2 · Accepted Answer · answered Jun 15 '21 at 14:26

2

Use double quote for the identifier, and one single quote inside:

SELECT 'Test 3' AS "O'Connell";

 O'Connell
-----------
 Test 3

answered Jun 15 '21 at 14:26

JGH

Ok. And when I use a field in my query like `SELECT field AS "O'Connell" FROM table;` ? – GeoGyro Jun 15 '21 at 14:29
1

@GeoGyro yes, like for any other alias :) – JGH Jun 15 '21 at 14:30
Except the answer will fail if `'Test 3'` is a column in a table. Identifiers need to be double quoted. Please revise your answer to be correct. – Adrian Klaver Jun 15 '21 at 14:44
@AdrianKlaver as in the question, `'test 3'` is a string, a constant if you want, that is selected as a column. This column should have an alias, and my answer address this point just fine. – JGH Jun 15 '21 at 14:48
Ok thanks. PgAdmin display confused me with the red syntax (see my post for screenshot). – GeoGyro Jun 15 '21 at 15:23
1

@GeoGyro you can trick the (not too bright) color parser by adding a single quote in comments after the alias `select myfield as "o'connor" /*'*/, myOtherField from...` – JGH Jun 15 '21 at 15:37

1 Answers1