### Initialize Async and Sync PostgREST Clients

Source: https://context7.com/supabase/postgrest-py/llms.txt

Demonstrates how to initialize both asynchronous and synchronous clients. Includes examples for context manager usage, authentication, schema switching, and manual connection management.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        client.auth("your-jwt-token")
        response = await client.from_("countries").select("*").execute()
        print(response.data)

    client = AsyncPostgrestClient("http://localhost:3000", schema="public", headers={"apikey": "your-api-key"})
    await client.aclose()

asyncio.run(main())
```

```python
from postgrest import SyncPostgrestClient

with SyncPostgrestClient("http://localhost:3000") as client:
    client.auth("your-jwt-token")
    response = client.from_("countries").select("*").execute()
    print(response.data)

client = SyncPostgrestClient("http://localhost:3000", timeout=60)
client.auth(token=None, username="user", password="password")
response = client.from_("users").select("id", "name", "email").execute()
client.aclose()
```

--------------------------------

### Install postgrest-py dependencies

Source: https://github.com/supabase/postgrest-py/blob/main/README.md

Commands to install the postgrest library using common Python package managers.

```shell
poetry add postgrest
```

```shell
pip install postgrest
```

--------------------------------

### Python: Ordering and Pagination with PostgREST-py

Source: https://context7.com/supabase/postgrest-py/llms.txt

Illustrates how to control the order of query results and implement pagination using PostgREST-py. Covers single and multiple column ordering, handling nulls, ordering on foreign tables, limiting results, and using offset and range for pagination. Includes an example of calculating total pages for pagination.

```python
import asyncio
from postgrest import AsyncPostgrestClient
from postgrest.types import CountMethod

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # Order by single column (ascending by default)
        response = await client.from_("products").select("*")\
            .order("name").execute()

        # Order descending
        response = await client.from_("products").select("*")\
            .order("price", desc=True).execute()

        # Order with nulls handling
        response = await client.from_("products").select("*")\
            .order("rating", desc=True, nullsfirst=False).execute()

        # Multiple ordering (chain order calls)
        response = await client.from_("products").select("*")\
            .order("category")\
            .order("price", desc=True).execute()
        # First by category asc, then by price desc within each category

        # Order on foreign table
        response = await client.from_("orders").select("*, order_items(*, products(name, price))")\
            .order("price", foreign_table="order_items.products", desc=True).execute()

        # Limit results
        response = await client.from_("products").select("*")\
            .limit(10).execute()

        # Limit on foreign table
        response = await client.from_("users").select("*, posts(*)")\
            .limit(5, foreign_table="posts").execute()
        # Limit posts per user to 5

        # Offset for pagination
        response = await client.from_("products").select("*")\
            .order("id")\
            .limit(20)\
            .offset(40).execute()  # Page 3 (items 41-60)

        # Range (combines offset and limit)
        response = await client.from_("products").select("*")\
            .order("id")\
            .range(0, 9).execute()  # First 10 items (0-9)

        response = await client.from_("products").select("*")\
            .order("id")\
            .range(10, 19).execute()  # Items 11-20

        # Pagination with count for total pages
        page = 3
        page_size = 20
        response = await client.from_("products").select("*", count=CountMethod.exact)\
            .order("id")\
            .range((page - 1) * page_size, page * page_size - 1).execute()
        total_pages = (response.count + page_size - 1) // page_size
        print(f"Page {page} of {total_pages}, showing {len(response.data)} items")

asyncio.run(main())
```

--------------------------------

### Perform CRUD operations

Source: https://github.com/supabase/postgrest-py/blob/main/README.md

Examples of inserting, reading, updating, and deleting records using the client.

```python
# Create
await client.from_("countries").insert({ "name": "Việt Nam", "capital": "Hà Nội" }).execute()

# Read
r = await client.from_("countries").select("id", "name").execute()

# Update
await client.from_("countries").update({"capital": "Hà Nội"}).eq("name", "Việt Nam").execute()

# Delete
await client.from_("countries").delete().eq("name", "Việt Nam").execute()
```

--------------------------------

### Python: Full-Text Search Filters with PostgREST-py

Source: https://context7.com/supabase/postgrest-py/llms.txt

Demonstrates using PostgREST-py client to perform full-text search queries on text columns. It covers basic text search (fts), plain text search (plfts), phrase search (phfts), and web search (wfts). Includes examples of combining full-text search with other standard filters like equality, range, ordering, and limiting.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # Full-text search (fts) - basic text search
        response = await client.from_("posts").select("*") \
            .fts("title", "python web development").execute()

        # Plain text search (plfts) - matches exact phrase
        response = await client.from_("articles").select("*") \
            .plfts("content", "machine learning").execute()

        # Phrase search (phfts) - words must appear in order
        response = await client.from_("documents").select("*") \
            .phfts("body", "quick brown fox").execute()

        # Web search (wfts) - Google-like search syntax
        response = await client.from_("posts").select("*") \
            .wfts("content", "python -django").execute()
        # Posts containing 'python' but NOT 'django'

        response = await client.from_("products").select("*") \
            .wfts("description", '"wireless headphones" OR "bluetooth earbuds"').execute()

        # Combining full-text search with other filters
        response = await client.from_("articles").select("*") \
            .fts("content", "python") \
            .eq("category", "programming") \
            .gte("published_at", "2024-01-01") \
            .order("published_at", desc=True) \
            .limit(10).execute()

asyncio.run(main())
```

--------------------------------

### Perform CRUD Operations

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Examples of standard CRUD operations including inserting, selecting specific columns, updating records with filters, and deleting records using the PostgREST client.

```python
# Insert
await client.from_("countries").insert({ "name": "Việt Nam", "capital": "Hà Nội" }).execute()

# Select specific columns
r = await client.from_("countries").select("id", "name").execute()

# Update with filter
await client.from_("countries").update({"capital": "Hà Nội"}).eq("name", "Việt Nam").execute()

# Delete with filter
await client.from_("countries").delete().eq("name", "Việt Nam").execute()
```

--------------------------------

### Execute Stored Procedure AsyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Executes a stored procedure asynchronously. Accepts the function name, parameters, and optional arguments for count, head, and get.

```python
await client.rpc("foobar", {"arg": "value"}).execute()
```

--------------------------------

### Enable Debug Logging for Postgrest-py Requests

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/logging.md

This Python code snippet demonstrates how to enable debug logging for the postgrest-py library. By setting the logging level to DEBUG and configuring basicConfig, you can observe the HTTP requests sent by the SyncPostgrestClient. This is useful for debugging API interactions. The output shows the structure of the GET requests made to the Supabase API.

```python
from logging import basicConfig, DEBUG
from postgrest import SyncPostgrestClient

basicConfig(level=DEBUG)

client  = SyncPostgrestClient(...)

client.from_("test").select("*").eq("a", "b").execute()
client.from_("test").select("*").eq("foo", "bar").eq("baz", "spam").execute()
```

--------------------------------

### Initialize AsyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/README.md

Demonstrates how to connect to a PostgREST server using the asynchronous client.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        r = await client.from_("countries").select("*").execute()
        countries = r.data

asyncio.run(main())
```

--------------------------------

### Execute Stored Procedure SyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Executes a stored procedure synchronously. Accepts the function name, parameters, and optional arguments for count, head, and get.

```python
client.rpc("foobar", {"arg": "value"}).execute()
```

--------------------------------

### Initialize AsyncPostgrestClient and Fetch Data

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Demonstrates how to instantiate the client with a base URL, authenticate using a Bearer token, and perform a basic select query to retrieve data from a table.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        client.auth("Bearer <token>")
        r = await client.from_("countries").select("*").execute()
        countries = r.data

asyncio.run(main())
```

--------------------------------

### Initialize AsyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Constructs an asynchronous PostgREST client. Requires a base URL and optionally accepts schema, headers, timeout, verification settings, proxy, and an HTTP client.

```python
from postgrest import AsyncPostgrestClient

client = AsyncPostgrestClient("http://localhost:3000")
```

--------------------------------

### INSERT - Create Data

Source: https://context7.com/supabase/postgrest-py/llms.txt

Insert single or multiple rows into a table. Supports returning the inserted data, getting counts, and bulk inserts with missing field handling.

```APIDOC
## insert() - Create Data

### Description
Insert single or multiple rows into a table. Supports returning the inserted data, getting counts, and bulk inserts with missing field handling.

### Method
POST

### Endpoint
/rest/v1/{table_name}

### Parameters
#### Query Parameters
- **columns** (string) - Optional - Comma-separated list of columns to insert data into.
- **count** (string) - Optional - Method for counting rows (e.g., `exact`, `planned`, `estimated`).
- **returning** (string) - Optional - Method for returning data (e.g., `representation`, `minimal`).
- **default_to_null** (boolean) - Optional - If true, missing fields will be set to null; otherwise, column defaults will be used.

### Request Body
- **data** (object or array of objects) - Required - The data to insert. Each object represents a row.

### Request Example
```json
{
  "name": "Vietnam",
  "capital": "Hanoi",
  "population": 97000000
}
```

### Response
#### Success Response (200)
- **data** (array of objects) - The inserted rows (if `returning` is set to `representation`).
- **count** (integer) - The number of rows affected (if `count` is specified).

#### Response Example
```json
{
  "data": [
    {
      "id": 1,
      "name": "Vietnam",
      "capital": "Hanoi",
      "population": 97000000
    }
  ],
  "count": 1
}
```
```

--------------------------------

### Connecting and Authenticating

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Demonstrates how to establish a connection to the PostgREST API and authenticate using a bearer token.

```APIDOC
## Connecting and Authenticating

### Description
Connect to the PostgREST API and authenticate with a bearer token.

### Method
N/A (Client Initialization)

### Endpoint
N/A (Client Initialization)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        client.auth("Bearer <token>")
        # ... further operations

asyncio.run(main())
```

### Response
N/A (Client initialization and authentication are client-side operations)

```

--------------------------------

### Initialize SyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Constructs a synchronous PostgREST client. Requires a base URL and optionally accepts schema, headers, timeout, verification settings, proxy, and an HTTP client.

```python
from postgrest import SyncPostgrestClient

client = SyncPostgrestClient("http://localhost:3000")
```

--------------------------------

### Manage Connection Lifecycle

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Demonstrates manual connection closure using aclose() and the recommended approach using an asynchronous context manager to ensure resources are cleaned up.

```python
# Manual close
await client.aclose()

# Context manager approach
async with AsyncPostgrestClient("url") as client:
    # run queries
    pass
```

--------------------------------

### Execute Stored Procedures via RPC

Source: https://context7.com/supabase/postgrest-py/llms.txt

Explains how to call PostgreSQL functions using the rpc method. It covers passing parameters, handling counts, using read-only GET requests, and filtering RPC results.

```python
import asyncio
from postgrest import AsyncPostgrestClient
from postgrest.types import CountMethod

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        response = await client.rpc("get_server_time", {}).execute()
        print(f"Server time: {response.data}")

        response = await client.rpc("search_products", {
            "search_term": "laptop",
            "min_price": 500,
            "max_price": 2000
        }).execute()
        print(f"Found {len(response.data)} products")

        response = await client.rpc(
            "get_active_users",
            {"department": "engineering"},
            count=CountMethod.exact
        ).execute()
        print(f"Active users: {response.count}")

        response = await client.rpc(
            "get_pending_orders",
            {},
            count=CountMethod.exact,
            head=True
        ).execute()
        print(f"Pending orders count: {response.count}")

        response = await client.rpc(
            "calculate_statistics",
            {"year": 2024},
            get=True
        ).execute()

        response = await client.rpc("get_all_products", {})\
            .select("id", "name", "price")\
            .gt("price", 50)\
            .order("price", desc=True)\
            .limit(10).execute()

        response = await client.rpc("get_user_profile", {"user_id": 123})\
            .single().execute()
        profile = response.data

asyncio.run(main())
```

--------------------------------

### Calling RPCs

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Demonstrates how to call stored procedures (RPCs) with and without arguments.

```APIDOC
## Calling RPCs

### Description
Execute stored procedures (RPCs) defined in your database. Supports calls with no arguments and with arguments.

### Method
POST

### Endpoint
`/rest/v1/rpc/{function_name}`

### Parameters
#### Path Parameters
- **function_name** (string) - Required - The name of the stored procedure to call.

#### Query Parameters
None

#### Request Body
- **body** (object) - Optional - An object containing the arguments for the RPC. If the RPC takes no arguments, this can be omitted or be an empty object.
  - **arg_name** (type) - Required/Optional - Name and type of the argument.

### Request Example
```python
# Calling an RPC with no arguments
await client.rpc("foo").execute()

# Calling an RPC with arguments
await client.rpc("bar", {"arg1": "value1", "arg2": "value2"}).execute()
```

### Response
#### Success Response (200)
- **data** (any) - The result returned by the RPC. The structure depends on the RPC's return type.
- **error** (object) - An error object if the RPC call failed.

#### Response Example
```json
{
  "data": {"message": "Success!"},
  "error": null
}
```
```

--------------------------------

### Fetching Data (SELECT)

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Illustrates how to select specific columns or all columns from a table.

```APIDOC
## Fetching Data (SELECT)

### Description
Fetch data from a table, with options to select specific columns or all columns.

### Method
GET

### Endpoint
`/rest/v1/{table_name}`

### Parameters
#### Path Parameters
- **table_name** (string) - Required - The name of the table to query.

#### Query Parameters
- **select** (string) - Optional - Comma-separated list of columns to select. Use `*` for all columns.

#### Request Body
None

### Request Example
```python
# Select all columns
r = await client.from_("countries").select("*").execute()
countries = r.data

# Select specific columns
r = await client.from_("countries").select("id, name").execute()
countries = r.data
```

### Response
#### Success Response (200)
- **data** (array) - An array of objects, where each object represents a row from the table.
- **error** (object) - An error object if the query failed.

#### Response Example
```json
{
  "data": [
    {"id": 1, "name": "United States", "capital": "Washington D.C."}
  ],
  "error": null
}
```
```

--------------------------------

### Ordering and Pagination

Source: https://context7.com/supabase/postgrest-py/llms.txt

Learn how to sort query results and implement pagination using order, limit, offset, and range methods.

```APIDOC
## Ordering and Pagination - order, limit, offset, range

Control the order of results and implement pagination for large result sets.

### GET /table

### Description
Retrieves data from a table with options for ordering, limiting, and paginating results.

### Method
GET

### Endpoint
`/table`

### Query Parameters
- **order** (string) - Optional - Specifies the column(s) to order by. Can include `desc` for descending order and `nullsfirst`/`nullslast` for null handling. Multiple orders can be chained.
- **limit** (integer) - Optional - Limits the number of results returned.
- **offset** (integer) - Optional - Skips a specified number of results, used for pagination.
- **range** (string) - Optional - Specifies a range of results to return, in the format `start-end` (inclusive).
- **count** (string) - Optional - Specifies how to count the total number of rows (e.g., 'exact', 'planned', 'estimated').

### Request Example
```python
# Order by single column (ascending by default)
response = await client.from_("products").select("*") \
    .order("name").execute()

# Order descending
response = await client.from_("products").select("*") \
    .order("price", desc=True).execute()

# Order with nulls handling
response = await client.from_("products").select("*") \
    .order("rating", desc=True, nullsfirst=False).execute()

# Multiple ordering (chain order calls)
response = await client.from_("products").select("*") \
    .order("category") \
    .order("price", desc=True).execute() # First by category asc, then by price desc within each category

# Order on foreign table
response = await client.from_("orders").select("*, order_items(*, products(name, price))") \
    .order("price", foreign_table="order_items.products", desc=True).execute()

# Limit results
response = await client.from_("products").select("*") \
    .limit(10).execute()

# Limit on foreign table
response = await client.from_("users").select("*, posts(*)") \
    .limit(5, foreign_table="posts").execute() # Limit posts per user to 5

# Offset for pagination
response = await client.from_("products").select("*") \
    .order("id") \
    .limit(20) \
    .offset(40).execute()  # Page 3 (items 41-60)

# Range (combines offset and limit)
response = await client.from_("products").select("*") \
    .order("id") \
    .range(0, 9).execute()  # First 10 items (0-9)

response = await client.from_("products").select("*") \
    .order("id") \
    .range(10, 19).execute()  # Items 11-20

# Pagination with count for total pages
page = 3
page_size = 20
response = await client.from_("products").select("*", count=CountMethod.exact) \
    .order("id") \
    .range((page - 1) * page_size, page * page_size - 1).execute()
total_pages = (response.count + page_size - 1) // page_size
print(f"Page {page} of {total_pages}, showing {len(response.data)} items")
```

### Response
#### Success Response (200)
- **data** (array) - An array of matching records, ordered and paginated as requested.
- **count** (integer) - The total number of records matching the query (if `count` parameter was used).

#### Response Example
```json
{
  "data": [
    {
      "id": 41,
      "name": "Product 41",
      "price": 100.00
    }
  ],
  "count": 100
}
```
```

--------------------------------

### Apply Filters and Execute Query with SyncFilterRequestBuilder

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/filters.md

Demonstrates how to chain various filter methods such as 'eq', 'gt', and 'like' on a SyncFilterRequestBuilder instance and execute the final query to retrieve results.

```python
from postgrest import SyncFilterRequestBuilder

# Assuming 'request_builder' is an instance of SyncFilterRequestBuilder
response = request_builder.eq("status", "active")\
    .gt("age", 18)\
    .like("name", "John%")\
    .execute()

print(response.data)
```

--------------------------------

### Authenticate Client with PostgREST-py

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Demonstrates how to authenticate a PostgREST client using either a bearer token or basic authentication credentials. The method requires a token or a username/password combination to establish a session.

```python
from postgrest import Client

client = Client("https://your-project.supabase.co/rest/v1")

# Using Bearer Token
client.auth("your-jwt-token")

# Using Basic Auth
client.auth(None, username="user", password="password")
```

--------------------------------

### Read Data with select()

Source: https://context7.com/supabase/postgrest-py/llms.txt

Shows how to retrieve data from tables using the select method. Covers column selection, counting rows, fetching related foreign table data, and performing head requests.

```python
import asyncio
from postgrest import AsyncPostgrestClient
from postgrest.types import CountMethod

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # Select specific columns and related data
        response = await client.from_("orders").select(
            "id",
            "customers(name, email)",
            "order_items(quantity, products(name, price))"
        ).execute()
        
        # Count rows without fetching data
        count_resp = await client.from_("products").select("*", count=CountMethod.exact, head=True).execute()
        print(f"Count: {count_resp.count}")

asyncio.run(main())
```

--------------------------------

### Insert Data with postgrest-py

Source: https://context7.com/supabase/postgrest-py/llms.txt

Demonstrates how to insert single or multiple rows into a table. Includes options for returning data, counting affected rows, and handling bulk inserts with default values.

```python
import asyncio
from postgrest import AsyncPostgrestClient
from postgrest.types import CountMethod, ReturnMethod

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # Insert single row
        response = await client.from_("countries").insert({
            "name": "Vietnam",
            "capital": "Hanoi",
            "population": 97000000
        }).execute()

        # Insert multiple rows
        response = await client.from_("countries").insert([
            {"name": "Thailand", "capital": "Bangkok"},
            {"name": "Malaysia", "capital": "Kuala Lumpur"},
            {"name": "Singapore", "capital": "Singapore"}
        ]).execute()

        # Insert with count
        response = await client.from_("products").insert(
            {"name": "New Product", "price": 29.99},
            count=CountMethod.exact
        ).execute()

        # Insert without returning data
        response = await client.from_("logs").insert(
            {"event": "user_login", "user_id": 123},
            returning=ReturnMethod.minimal
        ).execute()

        # Bulk insert with default values
        response = await client.from_("products").insert(
            [{"name": "A"}, {"name": "B", "price": 10}],
            default_to_null=False
        ).execute()

asyncio.run(main())
```

--------------------------------

### Inserting Data (INSERT)

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Shows how to insert a new row into a table.

```APIDOC
## Inserting Data (INSERT)

### Description
Insert a new row into a specified table.

### Method
POST

### Endpoint
`/rest/v1/{table_name}`

### Parameters
#### Path Parameters
- **table_name** (string) - Required - The name of the table to insert into.

#### Query Parameters
None

#### Request Body
- **body** (object) - Required - An object containing the key-value pairs for the new row.
  - **field** (type) - Required/Optional - Description of the field.

### Request Example
```python
await client.from_("countries").insert({"name": "Việt Nam", "capital": "Hà Nội"}).execute()
```

### Response
#### Success Response (200 or 201)
- **data** (array) - An array containing the newly inserted row(s).
- **error** (object) - An error object if the insertion failed.

#### Response Example
```json
{
  "data": [
    {"id": 2, "name": "Việt Nam", "capital": "Hà Nội"}
  ],
  "error": null
}
```
```

--------------------------------

### Pattern Matching Filters in PostgREST-py

Source: https://context7.com/supabase/postgrest-py/llms.txt

Demonstrates using pattern matching filters like 'like' and 'ilike' for flexible data searching with PostgREST-py. It covers case-sensitive and insensitive matching, wildcard usage, and matching against multiple patterns.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # LIKE - case sensitive pattern matching
        # % matches any sequence of characters
        # _ matches any single character
        response = await client.from_("products").select("*").like("name", "%phone%").execute()

        # Starts with pattern
        response = await client.from_("users").select("*").like("email", "%@gmail.com").execute()

        # Ends with pattern
        response = await client.from_("files").select("*").like("filename", "%.pdf").execute()

        # ILIKE - case insensitive pattern matching
        response = await client.from_("products").select("*").ilike("name", "%PHONE%").execute()
        # Matches "iPhone", "phone", "PHONE", etc.

        # Single character wildcard
        response = await client.from_("products").select("*").like("sku", "AB_123").execute()
        # Matches "AB1123", "AB2123", "ABCD123" (if 4 chars), etc.

        # like_any_of - matches any of the patterns
        response = await client.from_("products").select("*")
            .like_any_of("name", "%phone%,%tablet%,%laptop%").execute()

        # like_all_of - matches all patterns
        response = await client.from_("products").select("*")
            .like_all_of("description", "%waterproof%,%durable%").execute()

        # ilike_any_of - case insensitive any match
        response = await client.from_("users").select("*")
            .ilike_any_of("name", "%john%,%jane%").execute()

asyncio.run(main())
```

--------------------------------

### Full-Text Search Filters

Source: https://context7.com/supabase/postgrest-py/llms.txt

Demonstrates how to perform full-text search queries using various methods like fts, plfts, phfts, and wfts, and how to combine them with other filters.

```APIDOC
## Full-Text Search Filters - fts, plfts, phfts, wfts

Perform full-text search queries on text columns configured with text search indexes.

### GET /table

### Description
Performs a full-text search on a specified column.

### Method
GET

### Endpoint
`/table`

### Query Parameters
- **column_name** (string) - Required - The name of the text column to search.
- **query** (string) - Required - The search query string.
- **type** (string) - Optional - The type of full-text search to perform (e.g., 'fts', 'plfts', 'phfts', 'wfts'). Defaults to 'fts'.

### Request Example
```python
# Basic text search (fts)
response = await client.from_("posts").select("*") \
    .fts("title", "python web development").execute()

# Plain text search (plfts) - matches exact phrase
response = await client.from_("articles").select("*") \
    .plfts("content", "machine learning").execute()

# Phrase search (phfts) - words must appear in order
response = await client.from_("documents").select("*") \
    .phfts("body", "quick brown fox").execute()

# Web search (wfts) - Google-like search syntax
response = await client.from_("posts").select("*") \
    .wfts("content", "python -django").execute() # Posts containing 'python' but NOT 'django'

response = await client.from_("products").select("*") \
    .wfts("description", '"wireless headphones" OR "bluetooth earbuds"').execute()

# Combining full-text search with other filters
response = await client.from_("articles").select("*") \
    .fts("content", "python") \
    .eq("category", "programming") \
    .gte("published_at", "2024-01-01") \
    .order("published_at", desc=True) \
    .limit(10).execute()
```

### Response
#### Success Response (200)
- **data** (array) - An array of matching records.
- **count** (integer) - The total number of matching records (if requested).

#### Response Example
```json
{
  "data": [
    {
      "id": 1,
      "title": "Introduction to Python",
      "content": "Python is a versatile language..."
    }
  ],
  "count": 1
}
```
```

--------------------------------

### Comparison Filters in PostgREST-py

Source: https://context7.com/supabase/postgrest-py/llms.txt

Illustrates how to use comparison filters (eq, neq, gt, gte, lt, lte) with the PostgREST-py client to refine query results. These filters can be chained for complex AND conditions and applied to related tables.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # Equal to
        response = await client.from_("products").select("*").eq("category", "electronics").execute()

        # Not equal to
        response = await client.from_("products").select("*").neq("status", "discontinued").execute()

        # Greater than
        response = await client.from_("products").select("*").gt("price", 100).execute()

        # Greater than or equal to
        response = await client.from_("products").select("*").gte("stock", 10).execute()

        # Less than
        response = await client.from_("products").select("*").lt("price", 50).execute()

        # Less than or equal to
        response = await client.from_("products").select("*").lte("rating", 3).execute()

        # Combining multiple filters (AND logic)
        response = await client.from_("products").select("*")
            .gte("price", 10)
            .lte("price", 100)
            .eq("is_active", True)
            .gt("stock", 0).execute()
        # Gets active products priced $10-$100 that are in stock

        # Filter on related tables
        response = await client.from_("orders").select("*, products(*)")
            .eq("products.category", "electronics").execute()

asyncio.run(main())
```

--------------------------------

### Pattern Matching Filters API

Source: https://context7.com/supabase/postgrest-py/llms.txt

This section explains how to use `like` and `ilike` for pattern matching in queries, including case-sensitive, case-insensitive, and wildcard usage. It also covers `like_any_of`, `ilike_any_of`, and `like_all_of`.

```APIDOC
## SELECT /table with Pattern Matching Filters

### Description
Filter query results using SQL LIKE patterns for string matching. `like` is case-sensitive, while `ilike` is case-insensitive. Wildcards like `%` (any sequence) and `_` (any single character) can be used. Supports matching any or all patterns from a list.

### Method
GET

### Endpoint
`/table`

### Parameters
#### Query Parameters
- **select** (string) - Comma-separated list of columns to select. Use `*` for all columns.
- **like**(column, pattern) - Case-sensitive pattern matching (`%`, `_` wildcards).
- **ilike**(column, pattern) - Case-insensitive pattern matching (`%`, `_` wildcards).
- **like_any_of**(column, patterns) - Value matches any of the LIKE patterns (comma-separated string).
- **ilike_any_of**(column, patterns) - Value matches any of the ILIKE patterns (comma-separated string).
- **like_all_of**(column, patterns) - Value matches all of the LIKE patterns (comma-separated string).

### Request Example
```python
# LIKE - case sensitive pattern matching
response = await client.from_("products").select("*").like("name", "%phone%").execute() # Contains 'phone'
response = await client.from_("users").select("*").like("email", "%@gmail.com").execute() # Ends with '@gmail.com'
response = await client.from_("files").select("*").like("filename", "%.pdf").execute() # Ends with '.pdf'
response = await client.from_("products").select("*").like("sku", "AB_123").execute() # Matches 'ABX123' where X is any char

# ILIKE - case insensitive pattern matching
response = await client.from_("products").select("*").ilike("name", "%PHONE%").execute() # Matches 'iPhone', 'phone', 'PHONE'

# like_any_of - matches any of the patterns
response = await client.from_("products").select("*") \
    .like_any_of("name", "%phone%,%tablet%,%laptop%").execute()

# like_all_of - matches all patterns
response = await client.from_("products").select("*") \
    .like_all_of("description", "%waterproof%,%durable%").execute()

# ilike_any_of - case insensitive any match
response = await client.from_("users").select("*") \
    .ilike_any_of("name", "%john%,%jane%").execute()
```

### Response
#### Success Response (200)
- **data** (list) - List of rows matching the query.

#### Response Example
```json
[
  {"id": 5, "name": "Smartphone", "category": "electronics", ...}
]
```
```

--------------------------------

### Switch Schema AsyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Allows switching the active schema for the asynchronous PostgREST client. This affects subsequent queries made with the client.

```python
client.schema("custom_schema")
```

--------------------------------

### Debug Logging

Source: https://context7.com/supabase/postgrest-py/llms.txt

Instructions for enabling HTTP request logging to monitor communication with the PostgREST server.

```APIDOC
## Debug Logging

### Description
Enable standard Python logging to capture all outgoing HTTP requests and incoming responses for troubleshooting.

### Implementation
```python
from logging import basicConfig, DEBUG
basicConfig(level=DEBUG)
```

### Output
Logs will appear in the console showing the full URL, method, and status code for every request made by the client.
```

--------------------------------

### Using Negation and OR Filters

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/filters.md

Shows how to use the 'not_' property to negate a filter and the 'or_' method to combine multiple conditions using PostgREST syntax.

```python
response = request_builder.not_.eq("status", "archived")\
    .or_("name.eq.Alice,name.eq.Bob")\
    .execute()
```

--------------------------------

### Upsert Data with postgrest-py

Source: https://context7.com/supabase/postgrest-py/llms.txt

Explains how to perform upsert operations (INSERT ON CONFLICT DO UPDATE). Covers basic upsert, conflict column specification, bulk operations, and ignoring duplicates.

```python
import asyncio
from postgrest import AsyncPostgrestClient
from postgrest.types import CountMethod

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        # Basic upsert
        response = await client.from_("countries").upsert({
            "id": 1,
            "name": "Vietnam",
            "capital": "Hanoi"
        }).execute()

        # Upsert with on_conflict column
        response = await client.from_("users").upsert(
            {"email": "john@example.com", "name": "John Doe", "login_count": 1},
            on_conflict="email"
        ).execute()

        # Bulk upsert
        response = await client.from_("inventory").upsert([
            {"sku": "ABC123", "quantity": 100},
            {"sku": "DEF456", "quantity": 50},
            {"sku": "GHI789", "quantity": 75}
        ], on_conflict="sku").execute()

        # Upsert ignoring duplicates
        response = await client.from_("tags").upsert(
            [{"name": "python"}, {"name": "javascript"}, {"name": "rust"}],
            ignore_duplicates=True,
            on_conflict="name"
        ).execute()

        # Upsert with count
        response = await client.from_("products").upsert(
            {"id": 1, "name": "Widget", "stock": 100},
            count=CountMethod.exact
        ).execute()

asyncio.run(main())
```

--------------------------------

### POST /rpc/{func}

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Executes a stored procedure on the database via the PostgREST API.

```APIDOC
## POST /rpc/{func}

### Description
Executes a remote procedure (RPC) defined in the database. This endpoint allows passing parameters to the function and configuring the response behavior.

### Method
POST

### Endpoint
/rpc/{func}

### Parameters
#### Path Parameters
- **func** (string) - Required - The name of the remote procedure to run.

#### Request Body
- **params** (object) - Required - Key-value pairs representing arguments for the procedure.

### Request Example
{
  "arg": "value"
}

### Response
#### Success Response (200)
- **result** (any) - The result returned by the database procedure.

#### Response Example
{
  "result": "success"
}
```

--------------------------------

### Error Handling

Source: https://context7.com/supabase/postgrest-py/llms.txt

Details how to catch and handle APIError exceptions, including inspecting PostgreSQL error codes and details.

```APIDOC
## Error Handling

### Description
All failed requests raise an APIError exception. This allows developers to inspect specific PostgreSQL error codes, messages, and hints.

### Error Structure
- **message** (string) - Human-readable error description
- **code** (string) - PostgreSQL error code (e.g., 42501 for permission denied)
- **details** (string) - Additional error context
- **hint** (string) - Suggestion for resolution

### Example
```python
try:
    response = await client.from_("users").select("*").eq("id", 999).single().execute()
except APIError as e:
    print(e.code)
```
```

--------------------------------

### Closing the Connection

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Explains how to properly close the client connection, either manually or using a context manager.

```APIDOC
## Closing the Connection

### Description
Properly close the client connection to release resources. This can be done manually or automatically using an async context manager.

### Method
N/A (Client Method)

### Endpoint
N/A

### Parameters
None

### Request Example
```python
# Manual closing
await client.aclose()

# Using async with context manager (automatic closing)
async with AsyncPostgrestClient("url") as client:
    # run queries
# the client is closed when the async with block ends
```

### Response
N/A (Connection closing is a client-side operation)

```

--------------------------------

### Perform Table Operation AsyncPostgrestClient

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Initiates a query operation on a specified table using the asynchronous client. Returns an AsyncRequestBuilder for further query construction.

```python
client.from_("my_table")
```

--------------------------------

### Execute Remote Procedure Calls (RPC)

Source: https://github.com/supabase/postgrest-py/blob/main/docs/examples/basic_queries.md

Shows how to invoke database functions or stored procedures via RPC, both with and without arguments.

```python
# Simple RPC
await client.rpc("foo").execute()

# RPC with arguments
await client.rpc("bar", {"arg1": "value1", "arg2": "value2"}).execute()
```

--------------------------------

### Export Query Results as CSV

Source: https://context7.com/supabase/postgrest-py/llms.txt

Shows how to retrieve query results formatted as a CSV string instead of the default JSON format. This is useful for data exports and file generation.

```python
import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        response = await client.from_("products").select("id", "name", "price")\
            .order("name")\
            .csv().execute()

        csv_data = response.data
        print(csv_data)

        with open("products.csv", "w") as f:
            f.write(csv_data)

        response = await client.from_("orders").select(
            "id", "customer_name", "total", "created_at"
        ).gte("total", 100)\
            .order("created_at", desc=True)\
            .limit(1000)\
            .csv().execute()

asyncio.run(main())
```

--------------------------------

### Query Ordering and Pagination

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/request_builders.md

Methods for controlling the order of results and implementing pagination.

```APIDOC
## Query Ordering and Pagination

### limit(size: int, foreign_table: str | None = None)

Limit the number of rows returned by a query.

* **Parameters:**
  * **size** – The number of rows to be returned
  * **foreign_table** – Foreign table name to limit

* **Versionchanged**
  Changed in version 0.10.3: Allow limiting results returned for foreign tables with the foreign_table parameter.

### offset(size: int)

Set the starting row index returned by a query.

* **Parameters:**
  * **size** – The number of the row to start at

### order(column: str, desc: bool = False, nullsfirst: bool | None = None, foreign_table: str | None = None)

Sort the returned rows in some specific order.

* **Parameters:**
  * **column** – The column to order by
  * **desc** – Whether the rows should be ordered in descending order or not.
  * **nullsfirst** – nullsfirst
  * **foreign_table** – Foreign table name whose results are to be ordered.

* **Versionchanged**
  Changed in version 0.10.3: Allow ordering results for foreign tables with the foreign_table parameter.
```

--------------------------------

### POST /auth

Source: https://github.com/supabase/postgrest-py/blob/main/docs/api/client.md

Authenticates the client using either a bearer token or basic authentication credentials.

```APIDOC
## POST /auth

### Description
Authenticates the client with either a bearer token or basic authentication (username/password).

### Method
POST

### Endpoint
/auth

### Parameters
#### Request Body
- **token** (str) - Optional - Bearer token for authentication.
- **username** (str|bytes) - Optional - Username for basic authentication.
- **password** (str|bytes) - Optional - Password for basic authentication.

### Request Example
{
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
}

### Response
#### Success Response (200)
- **status** (string) - Indicates successful authentication.

#### Response Example
{
  "status": "authenticated"
}
```

--------------------------------

### Retrieve Single Row Results with single and maybe_single

Source: https://context7.com/supabase/postgrest-py/llms.txt

Demonstrates how to fetch exactly one row or at most one row from a database query. The single() method raises an error if no rows or multiple rows are returned, while maybe_single() returns None if no rows are found.

```python
import asyncio
from postgrest import AsyncPostgrestClient
from postgrest.exceptions import APIError

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        try:
            response = await client.from_("users").select("*")\
                .eq("id", 123)\
                .single().execute()
            user = response.data
            print(f"Found user: {user['name']}")
        except APIError as e:
            print(f"Error: {e.message}")

        response = await client.from_("users").select("*")\
            .eq("email", "user@example.com")\
            .maybe_single().execute()

        if response.data:
            print(f"User found: {response.data['name']}")
        else:
            print("No user found with that email")

        response = await client.from_("products").select("id")\
            .eq("sku", "ABC123")\
            .maybe_single().execute()

        exists = response.data is not None
        print(f"Product exists: {exists}")

asyncio.run(main())
```