Single quote in JSON field POSTGRESQL

Question

I am trying to manipulate a JSON with a single quote inside, and I am having some troubles:

1.- When I have a function, I cant pass as parameter a JSON string with a single quote inside: This is the function:

CREATE OR REPLACE FUNCTION public.give_me_text
(
  IN  text  json
)
RETURNS JSON AS
$$
DECLARE
v_text   varchar;
begin

RAISE NOTICE 'text: %', text;
v_text:=text || '- hello';
return v_text;
end
$$
LANGUAGE 'plpgsql';

By calling like this is working:

SELECT * FROM give_me_text
(
'{"es":"name 1","en":"name 2","de":"name 3","fr":"name 4","pt":"name 5"}'
);

But when I have got a single quote is not working:

SELECT * FROM give_me_text
    (
    '{"es":"nam'e 1","en":"name 2","de":"name 3","fr":"name 4","pt":"name 5"}'
    );

2.- When I am tryhing to insert JSON value with Single quote inside, it is giving me the same error: This is working:

INSERT INTO public.my_columns VALUES ('{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }');

But this is not working:

INSERT INTO public.my_columns VALUES ('{ "name": "Book's the First", "author": { "first_name": "Bob", "last_name": "White" } }');

Any ideas how to escape this single quote? Thanks

Ogee

Just make it a double single quote '' – Dropping

I have tried with REPLACE but always is giving me an error. I must put it by code because this string is from database, sometimes is coming with single quotes and sometimes not. – Ogee

Answer 1

In SQL, single quote must be escaped in a string. This is not a valid string:

'{"es":"nam'e 1"}'

because the string ends after "nam. You can escape the single quote by repeating it:

'{"es":"nam''e 1"}'

If you're doing dynamic SQL, you can pass parameters to execute:

execute 'insert into YourTable (col1) values ($1);' using json_var;

Misstep

But your string is not valid SQL. It has to come from somewhere? – Misstep

yes, it is a valid json string. It is coming from a json field. I loop with a cursor all the records,then i make a SELECT dinamycally and I concatenate this value, and then I have no valid string – Ogee