Query postgres integer array with Rails where values are not nil

Question

I'm trying to build a scope for my Contradiction model where the evaluation_parameters attribute only contains non NULL values. I have records where this attribute is completely empty {} or where one of the two values is nil {123, NULL} and I would like to not have these in my result.

Here is my migration:

add_column :contradictions, :evaluation_parameters, :integer, array: true, default: []

I already tried the following query which returns only records with an empty array

Contradiction.all.where.not("NULL = ANY (evaluation_parameters)")

Any suggestions on how I could do this ? Thank you :)

Yaro · Accepted Answer · 2017-11-24 16:28:44Z

0

First of all:

evaluation_parameters should probably be :text, not :integer.

The query you're looking for then is:

Contradiction.where("evaluation_parameters = '{}'")

This would fetch all contradictions which have evaluation_parameters equal to empty array.

answered Nov 24, 2017 at 16:28

Yaro

5683 silver badges20 bronze badges

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

1 Comment

Joel Mercier Over a year ago

Thank you for your input, however doing this only gives me the results with an empty array. What I want is all contradictions where both values in the array are NOT null (e.g. {123, 123}). I also don't see why I would have to convert to :text since I'm storing ids in this array.

Joel Mercier · Accepted Answer · 2017-11-27 09:08:26Z

0

I found an answer here Check if NULL exists in Postgres array My final query looks like this :

Contradiction.where.not(evaluation_parameters: '{}').where.not("-1 = ANY(evaluation_parameters) IS NULL")

answered Nov 27, 2017 at 9:08

Joel Mercier

536 bronze badges

Collectives™ on Stack Overflow

Query postgres integer array with Rails where values are not nil

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related