Query filter for not null

[kennethacohen]

This is incredibly basic (apologies), but for some reason I can't figure out how to compose a query that gets non-null values in a column

const { data: topRecipes } = await db
    .from<IRecipeSearch>("recipe_searches")
    .select("*")
    .not("external_recipe_id", "eq", null)

--

const { data: topRecipes } = await db
    .from<IRecipeSearch>("recipe_searches")
    .select("*")
    .neq("external_recipe_id", null)

Neither of the above seem to be working - would appreciate any help here!

Related: it would be nice to debug and see the query that SB is generating under the hood

[kennethacohen]

Actually the neq method above worked for me in a previous project...but doesn't seem to work now, so this may just be a bug?

[steve-chavez]

Check the is() filter.

[AnirudhBot]

Same issue! Doesn't work for me either

@steve-chavez can you please assist with this.

[steve-chavez]

Hm, @soedirgo was there something changed with postgrest-js is?

[soedirgo]

I don't think so, this should work as before. It'd help if you provide a reproducible example demonstrating the issue, otherwise it's hard to debug this.

[qqpann]

I tried it but doesn't work with TypeScript

[danya0365]

Test and worked

    if (isClaim !== undefined) {
      if (!isClaim) {
        query = query.is('claim_at', null);
      } else {
        query = query.not('claim_at', 'is', null);
      }
    }

[maheshj01]

Is it possible to have a non empty value in supabase database. I see that by default an empty cell is considered as "null" ([null] in database) which is treated as null in dart. So I was wondering if there is a way to consider an empty cell as "" (empty string) by default when we create the column?

[cdimitroulas]

You should set the column's default value to be empty string in that case.

[mike-wax]

If you're doing this with a JSONB column, make sure you use ->> (as opposed to ->) on the final key in the JSON

const result = await supabase
      .from('apps')
      .select()
      .not('details->schedule->>interval', 'is', null)

[takenoto]

I'm facing the same problem today (2023-07-07).
Platform: Flutter/Dart

Query:
supabase .from('vehicles_positions) .select('vehicle_id, latitude, longitude) .in_('vehicle_id', ids) .not('latitude','is',null)

I'm trying to get the vehicles where the latitude is not null.
Already tried:
.not('latitude','is',null)

.not('latitude','is',"null")

.not('latitude','is',"")

.neq('latitude',null)

.neq('latitude', 'null')

.neq('latitude', "")

I also tried variations such as 'NULL' and 'Null'.

[earonesty]

ive just changed all my "nullable" columns to default to empty string to work around this issue

[Krystian95]

Hi to all, is there any other way to archive "is not null" without using "default to empty string"?

[GaryAustin1]

.not('col','is', null)

Verified it is working in javascript client.

[Krystian95]

Hi, many thanks. I confirm that works also on Flutter

[flogy]

Working for me as well, thank you!

[Krystian95]

Hi, thanks. It works also on Flutter as well

[AndrewJudson]

What is the idiomatic way to do this in the Python client?

[Yumat10]

Did you ever find an answer for this?

[GravO8]

I think it is .not_.is_("columnName", "null")
Source

[AVivero]

It worked for me using query.not("columnName", "is", "NULL") and query.is("columnName", "NULL"). Hope that helps somebody!

[pmnzt]

while yes .not('col','is', null) works, but what if we need the 'is not null' in a .or ?
like .or('col.is_not.null')

[GaryAustin1]

The PostgREST documentation is the best source https://postgrest.org/en/v12/references/api/tables_views.html#logical-operators
So probably .or('col.not.is.null') or worst case .or('not.and(col.is.null)')

[RafaelCENG]

Hi im trying something like this

currentBuilder
                       .not("athlete_bikes", "is", null)
                       .in("athlete_bikes.bike_types.name", values as Array<string>,)

I'm trying to do a filter where athlete_bikes is not null and then filter the name based on the array. My problem is the .not. Now that i can have athlete_bikes null column the problem arise.

I should clarify that athlete_bikes is an other table.

[Mihai-github]

Hello,

Is anyone getting timeout from on supabase when performing the is("column_name", null)?