Closed arc4randall closed 1 month ago
You should use the isFilter for that case.
Thanks, this worked, but I don't think we should close this ticket because match accepts null values but they don't actually work.
Yeah, it might make sense to add an assersion for this.
Sorry this actually is broken again. isFilter was working but now it is returning this error
code:
"PGRST100"
details:
"unexpected "P" expecting null or trilean value (unknown, true, false)"
hashCode:
738743739
hint:
null
message:
""failed to parse filter (is.Payments)" (line 1, column 4)"
In my case I am passing in a nullable string. The string contains the word "Payments" so that's why the error says that. There is now a bug with match as well as isFilter. I now have to resort to fetching all of the objects and doing a local filter.
@arc4randall
There is now a bug with match as well as isFilter.
Neither one of them is a bug, but how SQL works, and Supabase and PostgREST are just adhering to how Postgres works. If anything, it's just a lack of assertion and documentation for people who are less familiar with how SQL works.
You can learn more about how the is
filter works on the Postgres docs here: https://www.postgresql.org/docs/current/functions-comparison.html
In your case, you have to conditionally apply either the eq
(or match
) filter and the isFilter
depending on whether your comparison value is null or not.
@arc4randall
There is now a bug with match as well as isFilter.
Neither one of them is a bug, but how SQL works, and Supabase and PostgREST are just adhering to how Postgres works. If anything, it's just a lack of assertion and documentation for people who are less familiar with how SQL works.
You can learn more about how the
is
filter works on the Postgres docs here: https://www.postgresql.org/docs/current/functions-comparison.htmlIn your case, you have to conditionally apply either the
eq
(ormatch
) filter and theisFilter
depending on whether your comparison value is null or not.
Right so I understand that SQL you would say column_name is null
and column name = "value"
. This is about improving the API design. The API does not need to be a literal translation of SQL. A good API design will make it easier and less error prone to interact with SQL. So there are a few bugs here that would improve the API design.
PostgrestFilterBuilder<T> isFilter(String column, Object? value) {
So this API design would be cleaner if it was just PostgrestFilterBuilder<T> isFilter(String column, bool? value) {
The match()
situation has already been addressed in a PR. The isFilter
should've only accepted a nullable boolean as you say.
Describe the bug When performing a select, I would like to include the equivalent of "where columnName is null". There are two options for where clause in Supabase flutter:
Supabase.instance.client.from('my_table').select('column_one, column_two').eq('column_three, null);
Supabase.instance.client.from('my_table').select('column_one, column_two').eq('column_three, null);
Returns 0 values even though running the equivalent query in Supabase SQL Editor returns results. Select eq with a value for column_three also returns all of the values. Only null returns no values.
To Reproduce Steps to reproduce the behavior:
Expected behavior I should be able to select where null. The workaround now is that I have to fetch all of the values and filter for null on the client.
Version (please complete the following information): On Linux/macOS Please run
dart pub deps | grep -E "supabase|gotrue|postgrest|storage_client|realtime_client|functions_client"
in your project directory and paste the output here.── supabase_flutter 2.5.2 │ ├── supabase 2.1.2 │ │ ├── functions_client 2.1.0 │ │ ├── gotrue 2.6.1 │ │ ├── postgrest 2.1.1 │ │ ├── realtime_client 2.0.4 │ │ ├── storage_client 2.0.1
Additional context Add any other context about the problem here.