Select Distinct in Postgresql erroring on unrelated column

Question

I'm struggling to write an SQL statement that does the following: Grab the most recent completed assessments of a group of users

One per user (distinct user_id)
Of a chosen group of users (user_id in array)
Only completed assessments (type_name is not null)
With all fields (will use later)

Closest attempt:

SELECT DISTINCT(user_id), assessments.*
FROM assessments
WHERE
  user_id IN (1,2,3,4,5,10)
  AND type_name IS NOT NULL
ORDER BY
  created_at DESC,
  user_id DESC

Current error:

could not identify an equality operator for type json

On:

assessments.*

I've run through a lot of different versions of this getting various errors, but this one I think should work but it seems to be trying to be distinct based off of all the columns instead of just user_id.

I'd really like it to work in Rails, but since I couldn't get that, I thought getting the plain SQL to work first.

UPDATE

Part of the problem could be that I have JSON columns on assessment. But since I'm using distinct on just user_id, it should ignore those JSON columns

Community · Accepted Answer · 2017-05-23 11:59:47Z

1

Try this:

Assesment.uniq(:user_id)
          .where(iuser_id: [1, 2, 3])
          .where
          .not(type_name: nil)

Update:

Since the problem is really in other columns than I would recommend to cast all your json columns to jsonb (pg 9.4 magic)

  class AlterJsonbToJsonAndBack < ActiveRecord::Migration                      
    def up                                                                     
      change_column :assessments, :column_name, 'jsonb USING CAST(column_name AS jsonb)'  
    end                                                                        

    def down                                                                   
      change_column :assessments, :column_name, 'json USING CAST(column_name AS json)'    
    end                                                                        
  end

how to use jsonb in rails

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Aug 19, 2015 at 22:22

Filip Bartuzi

5,9698 gold badges59 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Prats Over a year ago

I get the same error as before, I'll update the question to provide some more relevant data

Tom Prats Over a year ago

Rails seems to be ignoring the argument in uniq. I also tried distinct but the query it translates to is:

SELECT DISTINCT "assessments".* FROM "assessments" WHERE "assessments"."user_id" IN (1, 2, 3) AND ("assessments"."type_name" IS NOT NULL)  ORDER BY "assessments"."created_at" DESC

Collectives™ on Stack Overflow

Select Distinct in Postgresql erroring on unrelated column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related