Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
)
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
1  
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
add comment

1 Answer 1

Try this instead:

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

For some reason (unknown to me), the Postgres syntax shortcut :: for casts is not allowed in an index definition. It should work with the standard SQL form cast(expression AS type), though. This is completely independent of the json type, btw.

You can still use the syntax shortcut expression::type in expressions that utilize the index, though.

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
2  
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
add comment

Your Answer

 
discard

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.