Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save rogerluo410/5ba56b48f919b23eae1481384fc8e8d8 to your computer and use it in GitHub Desktop.
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
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