Write a query to select columns wrapped in single

问题:

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'

回答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

回答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)