I can't figure out for the life of me how to create an index on a property of my json column which is an integer.
I tried it this way (and also dozens of others)
CREATE INDEX user_reputation_idx ON users(("user"->>'reputation')::int)
It works just fine in a query (e.g. ORDER BY ("user"->>'reputation')::int
)
What am I missing?
UPDATE
I'm getting a simple syntax error, however, I really have no clue why.
ERROR: syntax error at or near "::"
LINE 1: ... user_reputation_idx ON users (("user"->>'reputation')::int)
The table definition is quite simple. It's just one column user
of typejson
.
So, looks like this:
CREATE TABLE users
(
"user" json
)
\d tbl
in psql), as well as the query you are trying to run. For starters, this related answer on SO may be of help. That one is for indexing a json array, though. Your case seems simpler ...json
property which holds simpletext
. Just for thisint
column it doesn't work. However, using it in a query to sort asint
works great. And btw, thanks for your great support here!