How to select a column to appear with two single quote in the field

Question

Here is my postgresql query

select 'insert into employee(ID_NUMBER,NAME,OFFICE) values ('''||ID_NUMBER||''','''||NAME||''','''||replace(DESIGNATION,'&','and')||''','''||replace(DEPT_NAME,'&','and')||''')' as col 
from icare_employee_view 
where id_number='201403241' 
order by name;

output

insert into employee(ID_NUMBER,NAME,OFFICE) values ('201403241','ABINUMAN, JOSEPHINE CALLO','Assistant AGrS Principal for Curriculum and Instruction','AGrS Principal's Office')

but I need 'AGrS Principal's Office' to be 'AGrS Principal''s Office'

but I need 'AGrS Principal's Office' to be 'AGrS Principal''s Office'

any suggestions or sol'n is highly appreciated on how to fix my PostgreSQL query

score 1 · Accepted Answer · answered Mar 31 '22 at 13:37

Hi check this from pgDocs:

quote_literal ( text ) → text

Returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 43.1.

quote_literal(E'O'Reilly') → 'O''Reilly'

How to select a column to appear with two single quote in the field

1 Answers1