How to return all the data if the filter is null In postgresql ?

Question

How to return all the data if the filter is null in postgresql ?

Sagar Lad · Answer

Hi Mallesh, You can check here for more info : https://stackoverflow.com/questions/29532680/filter-select-according-to-null-values

Rajanikant Hawaldar · Answer

What is Long? That's not a PostgreSQL data type.

Mallesh Mallu · Answer

Long

Rajanikant Hawaldar · Answer

What type is gradYearList? In addition, if you check data for null or being valid data, use COALESCE (gradYearList, whatever would be true)

Mallesh Mallu · Answer

@Query( value = "WITH conflict_values as 
" + " (SELECT coi.conflict_of_interest_flag_type, coi.flag_name, coi.conflict_of_interest_type, coi.id 
" + " FROM conflict_of_interest coi WHERE coi.university_id =:universityId)
" + " SELECT conflict_values.flag_name as flagName,count(d.id) FROM disclosure d INNER JOIN student_profile sp on d.student_id = sp.id
" + "						 AND sp.residency_status in (:residencyStatusList) AND sp.aid_status in (:financialAidStatusList) AND ((:gradYearList) IS NULL or sp.graduation_year in (:gradYearList))
" + " INNER JOIN disclosure_conflict_of_interests dcoi 
" + " on disclosure_id = d.id AND d.updated_at > (:timePeriod) AND d.deal_type_id in (:dealTypeIdList)
" + " RIGHT JOIN conflict_values on dcoi.conflict_of_interests_id = conflict_values.id group by flag_name order by count desc limit 5;", nativeQuery = true) List findTopDisclosureFlags( @Param("universityId") Long universityId, @Param("timePeriod") Timestamp timePeriod, @Param("residencyStatusList") List residencyStatusList, @Param("financialAidStatusList") List financialAidStatusList, @Param("dealTypeIdList") List dealTypeIdList, @Param("gradYearList") List gradYearList );

Rajanikant Hawaldar · Answer

As long as you don't show the query and the data structure, I can't tell you, what the problem might be.

Mallesh Mallu · Answer

I used it in my code but it is giving the error operator does not exist: integer = bytea

Rajanikant Hawaldar · Answer

IS NULL, probably.

How to return all the data if the filter is null In postgresql ?

Answers (8)