Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


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:

  "user" json
share|improve this question
You need to provide the verbatim error message and the (relevant part of) your table definition (\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 ... – Erwin Brandstetter Dec 5 '13 at 22:47
Thanks for your comment. I'm aware of that other answer and I'm already using this index for arrays as described there successfully. I also use another index on a json property which holds simple text. Just for this int column it doesn't work. However, using it in a query to sort as int works great. And btw, thanks for your great support here! – Christoph Dec 5 '13 at 23:41

Try this instead:

CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int));

The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se.

Either way, you can still use the syntax shortcut expression::type in expressions that utilize the index.

share|improve this answer
Great! That worked. There is just a closing parenthesis missing in your query. Thanks for saving my day again! – Christoph Dec 6 '13 at 9:26
@Christoph: Thanks, fixed. – Erwin Brandstetter Dec 6 '13 at 16:23
With enough brackets it should work. Eg CREATE INDEX user_reputation_idx2 ON users ( (("user"->>'reputation')::INT) ). I recall reading an answer in the pg lists where Tom Lane explained why the expression needed the extra brackets, but a quick search didn't turn it up. – bma Dec 6 '13 at 21:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.