Write a query to select columns wrapped in single quotes

Question

I am trying to write a select query which should return the column value wrapped in single quote. Say the column (ABC) has

Values: 123
        567

The query should return the

Output: '123'
        '567'

Tidbit

Select '\''||ABC||'\'' from table and Select '''ABC''' from table 1st query throws an exception nonstandard use of \' in a string literal 2nd query returns the column name in single quotes rather that the data 'ABC' – Tidbit

Answer 1

While dealing with numerical data, you can simply concatenate. NULL values stay NULL. But for character data (or similar) that might need escaping, use proper functions.

quote_nullable() or quote_literal() - depending on whether you have NULL values:

SELECT quote_nullable(val) AS quoted_val FROM tbl;

Details for quoting:

• Insert text with single quotes in PostgreSQL

Calabresi

Answer 2

I tend to escape the quote with another one, as in the standard SQL escape syntax:

nunks=# select '''I''m escaping a string''';
        ?column?         
-------------------------
 'I'm escaping a string'
(1 row)

When wrapping some output values, you'll have to concatenate with ||:

nunks=# create table numbers (number int);
CREATE TABLE

nunks=# insert into numbers values (151515);
INSERT 0 1

nunks=# select number from numbers;
 number 
--------
 151515
(1 row)

nunks=# select ''''||number||'''' from numbers;
 ?column? 
----------
 '151515'
(1 row)

Maybe you'll find it clearer using the E syntax:

nunks=# select E'\''||number||E'\'' from numbers;
 ?column? 
----------
 '151515'
(1 row)

Smackdab