PostgreSQL example:
CREATE OR REPLACE FUNCTION f_random_text(
length integer
)
RETURNS text AS
$body$
WITH chars AS (
SELECT unnest(string_to_array('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9', ' ')) AS _char
),
charlist AS
(
SELECT _char FROM chars ORDER BY random() LIMIT $1
)
SELECT string_agg(_char, '')
FROM charlist
;
$body$
LANGUAGE sql;
DROP TABLE IF EXISTS tmp_test;
CREATE TEMPORARY TABLE tmp_test (
id serial,
data text default f_random_text(12)
);
INSERT INTO tmp_test
VALUES
(DEFAULT, DEFAULT),
(DEFAULT, DEFAULT)
;
SELECT * FROM tmp_test;
id | data
----+--------------
1 | RYMUJH4E0NIQ
2 | 7U4029BOKAEJ
(2 rows)
Apparently you can do this. (Of course, you can add other characters as well, or use other random string generator as well - like this, for example.)
This does not work because it's trying to cast a jsonb
value to integer
.
select data->'name' as name from persons where cast(data->'age' as int) > 25
This would actually work:
SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;
Or shorter:
SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;
And this:
SELECT data->'name' AS name FROM persons WHERE data->>'name' > 'Jenny';
Seems like confusion with the two operators ->
and ->>
and operator precedence. The cast ::
binds stronger than the json(b) operators.
Figure out type dynamically
This is the more interesting part of your question:
the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?
SQL is a strictly typed language, it does not allow the same expression to evaluate to integer
in one row and to text
in the next. But since you are only interested in the boolean
result of the test, you can get around this restriction with a CASE
expression that forks depending on the result of jsonb_typeof()
:
SELECT data->'name'
FROM persons
WHERE CASE jsonb_typeof(data->'age')
WHEN 'number' THEN (data->>'age')::numeric > '25' -- treated as numeric
WHEN 'string' THEN data->>'age' > 'age_level_3' -- treated as text
WHEN 'boolean' THEN (data->>'age')::bool -- use boolean directly (example)
ELSE FALSE -- remaining: array, object, null
END;
An untyped string literal to the right of the >
operator is coerced to the respective type of the value to the left automatically. If you put a typed value there, the type has to match or you have to cast it explicitly - unless there is adequate implicit cast registered in the system.
If you know that all numeric values are actually integer
, you can also:
... (data->>'age')::int > 25 ...
Best Answer
That's the same as with any other default value: