postgresql text to json conversion

Question

I have in the database a table query_config, which will contain scripts for querying, as well as other configuration values​​. This is the reason why I use json type. Table as follows:

CREATE TABLE query_config 
(
   id integer, 
   execute_query json 
); 

In this table I want to insert eg:

INSERT INTO query_config(id, execute_query)
    VALUES (4, ('{"query": ' || 
    '"select *
    from tests
    where tests.id > 5
    order by moment desc"}')::json);

But I keep getting the error:

ERROR: invalid input syntax for type json 
DETAIL: Character with value 0x0a must be escaped.

What am I doing wrong please and how do I escape newline character?

Answer 1

Use to_json to make it valid json and dollar quoting so it will swallow any text in the query

select format('{%s: %s}',
    to_json('query'::text), 
    to_json($query$
        select *
        from tests
        where tests.id > 5
        order by moment desc
    $query$::text)
)::json;
                                                       format                                                        
---------------------------------------------------------------------------------------------------------------------
 {"query": "\n        select *\n        from tests\n        where tests.id > 5\n        order by moment desc\n    "}

http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-TABLE

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

The format function is just to make it easy on the eyes

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER

Answer 2

it will work if you remove new lines, in other words, put the json value on a single line like:

INSERT INTO query_config(id, execute_query)
    VALUES (4, ('{"query":' || '"select * from tests where tests.id > 5 order by moment desc"}')::json);