How to convert sql-text to jsonb-string?

To convert untyped string literals, that are not wrapped in double-quotes to jsonb (or json), use the to_jsonb() (or to_json()) function:

SELECT to_jsonb(text 'a123');

Note that the input has to be a string type (text, varchar, ...), not an untyped literal. That's how Postgres knows you want a JSON string.

The above text 'a123' is one way to cast an untyped literal. There are others:

• Postgres data type cast

For a direct cast to json(b), Postgres expects valid JSON literals (with double-quoted strings):

SELECT '"a123"'::jsonb;  

To translate each value to a specific JSON primitive, you can cast conditionally before the conversion. Example:

SELECT p, CASE WHEN i>2 THEN to_jsonb(p::numeric) ELSE to_jsonb(p) END AS x
FROM   unnest('{ab,12,12,1.2}'::text[]) WITH ORDINALITY t(p,i);

select '"a123"'::text::jsonb = BAD string because quoted

To be precise, the result is not a string, but a jsonb value containing a JSON string. To get the string as Postgres data type text, you need the ->> operator:

select 'a123'::text  = ('{"x":"a123"}'::jsonb)->>'x'

Or (comparing JSON values):

select '"a123"'::jsonb = ('{"x":"a123"}'::jsonb)->'x';

I need '123' and 'a123' as pure JSONb strings.

So:

SELECT '"123"'::jsonb, '"a123"'::jsonb;

Both contain JSON strings.

This also works:

SELECT '123'::jsonb;

.. but contains a JSON numeric.

But this does not work:

SELECT 'a123'::jsonb;  -- error

.. because it's not a valid numeric literal.

The manual has a table of mappings between JSON Primitive Types and Corresponding PostgreSQL Types