How escape single quote ‘ in postgresql

To escape single quote in SQL Server and in PostgreSQL doubling them up ‘ as showed in examples below. It’s useful with SQL insert and update command:

Escape with the backslash \‘ is not prefereable  \’.

It’s possible to use dollar-quote string $ PostgreSQL: Documentation: 9.3: Lexical Structure:   ‘escape ” dollar-quote test ””’   ->  $$escape ‘ dollar-quote test ”$$  -> $token$escape ‘ dollar-quote test ”$token$

1) dubling up single quote ‘

INSERT INTO _aquery ( itemid, tcontent, title ) 
 VALUES ( 8503, SELECT * FROM information_schema.tables WHERE table_schema=''public'' and table_type=''BASE TABLE''', 'Show tables view sequences...' ) ;

Result:

itemid | tcontent	                                                                               | title
8502	 SELECT * FROM information_schema.tables WHERE table_schema='public' and table_type=BASE TABLE	 Show tables view sequences ...

1)

2)

INSERT INTO _aquery ( itemid, tcontent, title ) 
 VALUES (  8505, 
'SELECT n.nspname as "Schema",
       c.relname as "Name",
       CASE c.relkind WHEN ''r'' THEN ''table'' WHEN ''v'' THEN ''view'' WHEN ''i'' THEN ''index'' WHEN ''S'' THEN ''sequence'' WHEN ''s'' THEN ''special'' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN (''r'','''')
  AND n.nspname <> ''pg_catalog''
  AND n.nspname <> ''information_schema''
  AND n.nspname !~ ''^pg_toast''
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;', 
'Show tables view sequences...' ) ;

Mysql use backslash \ to escape signle quote \

42 O\‘Connor Street

Risorse:

plpgsql 
Annunci