-
-
Save rogerluo410/5ba56b48f919b23eae1481384fc8e8d8 to your computer and use it in GitHub Desktop.
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: could not identify an equality operator for type json
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Explanation: | |
``` | |
The reason behind this, is that in PostgreSQL (up to 9.3) there is no equality operator defined for json (i.e. val1::json = val2::json will always throw this exception) -- in 9.4 there will be one for the jsonb type. | |
One workaround is, you can cast your json field to text. But that won't cover all json equalitions. f.ex. {"a":1,"b":2} should be equal to {"b":2,"a":1}, but won't be equal if casted to text. | |
``` | |
Possible ways to fix: | |
1) Migrate to Postgresql 9.4 and use .jsonb type | |
2) Postgresql 9.3 ways to solve: | |
2.1) if you have a primary key for that table -- which should be - then you can use the DISTINCT ON (<expressions>) form: | |
``` | |
u.profiles.select("DISTINCT ON (profiles.id) profiles.*") | |
``` | |
* But: | |
``` | |
DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [...] Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [...] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). | |
``` | |
So need to use "profiles.id" a the first option for ORDER BY (which can change order you need) | |
But you can also try to fix it using subquery: | |
``` | |
SELECT * | |
FROM ( | |
SELECT DISTINCT ON (address_id) * | |
FROM purchases | |
WHERE product_id = 1 | |
ORDER BY address_id, purchased_at DESC -- to get "latest" row per addr. | |
) p | |
ORDER BY purchased_at DESC; | |
``` | |
2.2) use Group instead of Distinct | |
``` | |
.group("profiles.id") | |
``` |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment