Filters

Filters allow only rows that meet specific criteria to be returned.

Filters can be used for select(), update(), upsert(), and delete() queries.

eq

Only matches rows where the column value equals the specified value.

To check whether a column value is NULL, use .is() instead of eq.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where title equals "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .eq("title", "Tencent CloudBase");

neq

Only matches rows where the column value does not equal the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where title is not equal to "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .neq("title", "Tencent CloudBase");

gt

Only matches rows where the column value is greater than the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is greater than 2
const { data, error } = await db.from("articles").select().gt("id", 2);

gte

Only matches rows where the column value is greater than or equal to the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is greater than or equal to 2
const { data, error } = await db.from("articles").select().gte("id", 2);

lt

Only matches rows where the column value is less than the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is less than 2
const { data, error } = await db.from("articles").select().lt("id", 2);

lte

Only matches rows where the column value is less than or equal to the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is less than or equal to 2
const { data, error } = await db.from("articles").select().lte("id", 2);

like

Only matches rows where the column value matches a specific pattern (case sensitivity depends on collation rules).

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
pattern	string	Required	Pattern to match

Code Example

// Query all records from the articles table where title contains "cloudbase"
const { data, error } = await db
  .from("articles")
  .select()
  .like("title", "%cloudbase%");

ilike

Only matches rows where the column value matches a specific pattern (case-insensitive, regardless of collation rules).

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
pattern	string	Required	Pattern to match

Code Example

// Query all records from the articles table where title contains "cloudbase" (case-insensitive)
const { data, error } = await db
  .from("articles")
  .select()
  .ilike("title", "%cloudbase%");

is

Only matches rows where the column value equals the specified value.

For non-boolean columns, it is mainly used to check whether the column value is NULL.

For boolean columns, it can also be set to true or false, behaving the same as .eq().

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	Object	Required	Value for filtering

Code Example

// Query all records from the articles table where title is null
const { data, error } = await db.from("articles").select().is("title", null);

in

Only matches rows where the column value is contained in the specified array.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
values	Array	Required	Array of values to filter

Code Example

// Query all records from the articles table where title is in the specified array ["Tencent CloudBase", "CloudBase"]
const { data, error } = await db
  .from("articles")
  .select()
  .in("title", ["Tencent CloudBase", "CloudBase"]);

contains

Only matches rows where the column value contains the specified value.

Applies to array columns, range columns, and JSONB columns.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	Array|Object	Required	Value for filtering

Code Example

// Query all records from the articles table where tags contains ["tech", "cloud"] (array column)
const { data, error } = await db
  .from("articles")
  .select()
  .contains("tags", ["tech", "cloud"]);

// Query all records from the articles table where metadata contains the specified JSON object (JSONB column)
const { data, error } = await db
  .from("articles")
  .select()
  .contains("metadata", { category: "tech" });

containedBy

Only matches rows where the column value is contained by the specified value.

Applies to array columns, range columns, and JSONB columns.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	Array|Object	Required	Value for filtering

Code Example

// Query all records from the articles table where tags is contained by ["tech", "cloud", "frontend", "backend"]
const { data, error } = await db
  .from("articles")
  .select()
  .containedBy("tags", ["tech", "cloud", "frontend", "backend"]);

overlaps

Only matches rows where the column value overlaps with the specified value.

Applies to array columns and range columns.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	Array|string	Required	Value for filtering

Code Example

// Query all records from the articles table where tags overlaps with ["tech", "frontend"]
const { data, error } = await db
  .from("articles")
  .select()
  .overlaps("tags", ["tech", "frontend"]);

textSearch

Only matches rows where the tsvector column value satisfies the specified tsquery full-text search condition.

Parameters

Parameter	Type	Required	Description
column	string	Required	Text search column to filter
query	string	Required	Full-text search query to match
options	object	Optional	Named parameters
options.config	string	Optional	Text search configuration (e.g., 'english', 'simple')
options.type	string	Optional	Search type: 'plain' (default), 'phrase', or 'websearch'

Code Example

// Full-text search for records where content column contains "cloud development"
const { data, error } = await db
  .from("articles")
  .select()
  .textSearch("content", "cloud development");

// Full-text search using websearch syntax
const { data, error } = await db
  .from("articles")
  .select()
  .textSearch("content", "'cloud development' OR 'cloud computing'", {
    type: "websearch",
    config: "english",
  });

rangeGt

Only applies to range columns. Only matches rows where every element in the column is greater than any element in the range.

Parameters

Parameter	Type	Required	Description
column	string	Required	Range column to filter
range	string	Required	Range for filtering

Code Example

// Query all records from the articles table where created_at is greater than the range "[2023-01-01 00:00, 2023-01-02 00:00)"
const { data, error } = await db
  .from("articles")
  .select()
  .rangeGt("created_at", "[2023-01-01 00:00, 2023-01-02 00:00)");

rangeGte

Only applies to range columns. Only matches rows where every element in the column is contained in the range or greater than any element in the range.

Parameters

Parameter	Type	Required	Description
column	string	Required	Range column to filter
range	string	Required	Range for filtering

Code Example

// Query all records from the articles table where created_at is greater than or equal to the range "[2023-01-01 08:30, 2023-01-01 09:30)"
const { data, error } = await db
  .from("articles")
  .select()
  .rangeGte("created_at", "[2023-01-01 08:30, 2023-01-01 09:30)");

rangeLt

Only applies to range columns. Only matches rows where every element in the column is less than any element in the range.

Parameters

Parameter	Type	Required	Description
column	string	Required	Range column to filter
range	string	Required	Range for filtering

Code Example

// Query all records from the articles table where created_at is less than the range "[2023-01-01 12:00, 2023-01-01 13:00)"
const { data, error } = await db
  .from("articles")
  .select()
  .rangeLt("created_at", "[2023-01-01 12:00, 2023-01-01 13:00)");

rangeLte

Only applies to range columns. Only matches rows where every element in the column is contained in the range or less than any element in the range.

Parameters

Parameter	Type	Required	Description
column	string	Required	Range column to filter
range	string	Required	Range for filtering

Code Example

// Query all records from the articles table where created_at is less than or equal to the range "[2023-01-01 14:00, 2023-01-01 16:00)"
const { data, error } = await db
  .from("articles")
  .select()
  .rangeLte("created_at", "[2023-01-01 14:00, 2023-01-01 16:00)");

rangeAdjacent

Only applies to range columns. Only matches rows where the column range is mutually exclusive with the specified range and has no overlapping elements.

Parameters

Parameter	Type	Required	Description
column	string	Required	Range column to filter
range	string	Required	Range for filtering

Code Example

// Query all records from the articles table where created_at is mutually exclusive with the range "[2023-01-01 12:00, 2023-01-01 13:00)" and has no overlapping elements
const { data, error } = await db
  .from("articles")
  .select()
  .rangeAdjacent("created_at", "[2023-01-01 12:00, 2023-01-01 13:00)");

match

Only matches rows where every column in the query key equals its associated value, equivalent to a shorthand for multiple .eq() calls.

Parameters

Parameter	Type	Required	Description
query	Record<ColumnName, Row['ColumnName']>	Required	Filter object, column names as keys mapped to their filter values

Code Example

// Query all records from the articles table where id equals 2 and title equals "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .match({ id: 2, title: "Tencent CloudBase" });

not

Only matches rows that do not satisfy the filter condition.

Unlike most filters, the operator and value are used as-is and must follow PostgreSQL syntax. You also need to ensure they are properly escaped.

not() expects raw PostgreSQL syntax as the filter value.

.not('id', 'in', '(5,6,7)')  // Use `()` for `in` filter
.not('name', 'like', '%test%')  // Use `not like` for pattern matching

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
operator	string	Required	Filter operator to negate, following PostgreSQL syntax
value	any	Required	Filter value, following PostgreSQL syntax

Code Example

// Query all records from the articles table where title is not null
const { data, error } = await db
  .from("articles")
  .select()
  .not("title", "is", null);

or

Only matches rows that satisfy at least one filter condition.

Unlike most filters, the filters are used as-is and must follow PostgreSQL syntax. You also need to ensure they are properly escaped.

Currently, it is not possible to perform .or() filtering across multiple tables.

or() expects raw PostgreSQL syntax as filter names and values.

.or('id.in.(5,6,7), name.like.%test%')  // Use `()` for `in` filter, use `like` and `%` for pattern matching
.or('id.in.(5,6,7), name.not.like.%test%')  // Use `not.like` for negated pattern matching

Parameters

Parameter	Type	Required	Description
filters	string	Required	Filters to use, following PostgreSQL syntax
options	object	Required	Named parameters
options.referencedTable	string	Optional	Set to filter on the referenced table instead of the parent table

Code Example

// Query all records from the articles table where id equals 2 or title equals "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .or(`id.eq.2,title.eq.Tencent CloudBase`);

filter

Only matches rows that satisfy the filter condition. This is an escape hatch - you should use specific filter methods whenever possible.

Unlike most filters, the operator and value are used as-is and must follow PostgreSQL syntax. You also need to ensure they are properly escaped.

filter() expects raw PostgreSQL syntax as the filter value.

.filter('id', 'in', '(5,6,7)')  // Use `()` for `in` filter
.filter('name', 'like', '%test%')  // Use `like` for pattern matching
.filter('name', 'not.like', '%test%')  // Use `not.like` for negated pattern matching

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
operator	string	Required	Filter operator, following PostgreSQL syntax
value	any	Required	Filter value, following PostgreSQL syntax

Code Example

// Query records where title is in the specified value list
// Query all records from the articles table where title is in the specified value list ["Tencent CloudBase", "CloudBase"]
const { data, error } = await db
  .from("articles")
  .select()
  .filter("title", "in", "(Tencent CloudBase,CloudBase)");

// Filter on a referenced table
// Query all records from the articles table where the associated categories.name equals "Technology"
const { data, error } = await db
  .from("articles")
  .select()
  .filter("categories.name", "eq", "Technology");