How to fix PGRST116: More than one row returned by a subquery used as an expression in Supabase

SupabaseINTERMEDIATEHIGHUpdated Dec 29, 2025· 158 views

This error occurs when a scalar subquery in your SQL query returns multiple rows instead of the expected single row. In Supabase, this commonly happens during INSERT/UPDATE operations with triggers, RLS policies, or computed columns.

Quick fix

Fixes PGRST116: More than one row returned by a subquery used as an expression

// Check Supabase logs for the full SQL query
// The error message typically includes the query text
ERROR: more than one row returned by a subquery used as an expression
  1. Identify the problematic subquery
  2. Replace = with IN for multiple row scenarios
  3. Add LIMIT 1 to constrain the subquery
  4. Use aggregate functions for single-value results
supabase — error
// Check Supabase logs for the full SQL query
// The error message typically includes the query text
ERROR: more than one row returned by a subquery used as an expression
PGRST116: More than one row returned by a subquery used as an expression

What this error means

PostgreSQL enforces that scalar subqueries (subqueries used in comparison or assignment contexts) must return exactly one row. When a subquery returns zero rows or multiple rows in a context expecting a single value, PostgreSQL raises an error. In Supabase, this typically manifests as PGRST116 when using the Supabase JavaScript client with methods like .single() or when database-level operations trigger this PostgreSQL error. The error signals a mismatch between your query logic and PostgreSQL's strict single-value expectations.

Typical symptoms

  • Requests fail with PGRST116 error code during INSERT or UPDATE operations
  • Operations work in local Postgres but fail in Supabase production
  • Using .single() method returns unexpected error even though you expect one row
  • Database triggers or RLS policies cause the error intermittently
  • Edge function deployments trigger the error that didn't occur locally
  • The error message references 'subquery used as an expression'

Common causes

  1. A subquery in your SQL (trigger, RLS policy, or computed column) returns multiple rows when PostgreSQL expects exactly one
  2. Using = operator with a subquery that matches multiple records instead of using IN or ANY
  3. Missing or incorrect WHERE clause in the subquery that should narrow results to one row
  4. Row Level Security (RLS) policy containing a subquery that returns multiple rows
  5. Database trigger using a subquery without proper filtering logic
  6. UPDATE statement using a correlated subquery that matches multiple rows

How to fix "PGRST116: More than one row returned by a subquery used as an expression"

1Identify the problematic subquery

Check your database schema for triggers, RLS policies, or computed columns that might contain the subquery. You can also check the full error message in Supabase logs:

bash
// Check Supabase logs for the full SQL query
// The error message typically includes the query text
ERROR: more than one row returned by a subquery used as an expression

Identify which SQL operation (INSERT, UPDATE, or trigger) is causing the issue.

2Replace = with IN for multiple row scenarios

If your subquery is expected to return multiple rows, use the IN operator instead of =:

sql
-- WRONG: Uses = with subquery
UPDATE users SET status = (SELECT status FROM roles WHERE user_type = $1);

-- CORRECT: Uses IN instead
UPDATE users SET status = ANY(SELECT status FROM roles WHERE user_type = $1);

Alternatively, use IN for simpler cases:

sql
WHERE column IN (SELECT column FROM table)
3Add LIMIT 1 to constrain the subquery

If the subquery should only return one row, explicitly limit it:

sql
-- BEFORE: Can return multiple rows
UPDATE products SET price = (SELECT price FROM pricing WHERE category = $1);

-- AFTER: Explicitly limited to 1 row
UPDATE products SET price = (SELECT price FROM pricing WHERE category = $1 LIMIT 1);

However, LIMIT alone doesn't guarantee the correct row. Ensure your WHERE clause uniquely identifies the row.

4Use aggregate functions for single-value results

If you need a calculated single value from multiple rows, use aggregate functions:

sql
-- BEFORE: Ambiguous which row to return
UPDATE inventory SET stock = (SELECT stock FROM warehouse WHERE region = $1);

-- AFTER: Explicitly aggregate
UPDATE inventory SET stock = (SELECT SUM(stock) FROM warehouse WHERE region = $1);
-- Or:
UPDATE inventory SET price = (SELECT MAX(price) FROM pricing WHERE category = $1);
5Add precise WHERE clause to filter to one row

The most common fix: ensure your subquery's WHERE clause uniquely identifies one row:

sql
-- BEFORE: May match multiple roles
UPDATE users SET role_name = (SELECT name FROM roles WHERE department = $1);

-- AFTER: Unique constraint ensures one row
UPDATE users SET role_name = (SELECT name FROM roles WHERE id = $2);

Make sure the column(s) in your WHERE clause are unique or have appropriate indexes.

6Check RLS policies for multiple-row subqueries

If the error occurs with RLS policies, review your policy definitions:

sql
-- PROBLEMATIC: RLS policy with subquery returning multiple rows
CREATE POLICY "users can read own data" ON users
  FOR SELECT
  USING (id IN (SELECT user_id FROM permissions)); -- Could be multiple!

-- FIXED: Add constraint to single row
CREATE POLICY "users can read own data" ON users
  FOR SELECT
  USING (id = (SELECT user_id FROM permissions WHERE user_id = auth.uid() LIMIT 1));
7Test with Supabase SQL Editor

Before running your application, test the corrected query in the Supabase SQL Editor:

1. Go to Supabase Dashboard → SQL Editor
2. Write your corrected query
3. Click "Run" to verify it executes without the PGRST116 error
4. Test with sample data that matches your application's use case

Advanced notes

Correlated Subqueries: When using correlated subqueries in UPDATE statements, ensure the correlation properly links each row to exactly one result:

sql
-- CORRECT: Correlated subquery with proper WHERE
UPDATE users u
SET last_order_date = (
  SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id
);

Performance Consideration: Complex subqueries can be slow. Consider using JOINs instead:

sql
-- Instead of subquery:
UPDATE products SET stock = (SELECT SUM(qty) FROM inventory WHERE product_id = products.id);

-- Use JOIN:
UPDATE products p
SET stock = i.total_qty
FROM (SELECT product_id, SUM(qty) as total_qty FROM inventory GROUP BY product_id) i
WHERE p.id = i.product_id;

Trigger Context: Database triggers often trigger this error. Use NEW and OLD references properly:

sql
CREATE FUNCTION update_user_summary()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE users SET order_count = (SELECT COUNT(*) FROM orders WHERE user_id = NEW.user_id) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Frequently asked questions

What does "PGRST116: More than one row returned by a subquery used as an expressi" mean?
PostgreSQL enforces that scalar subqueries (subqueries used in comparison or assignment contexts) must return exactly one row. When a subquery returns zero rows or multiple rows in a context expecting a single value, PostgreSQL raises an error. single() or when database-level operations trigger this PostgreSQL error.
How do I fix "PGRST116: More than one row returned by a subquery used as an expressi"?
This error occurs when a scalar subquery in your SQL query returns multiple rows instead of the expected single row. In Supabase, this commonly happens during INSERT/UPDATE operations with triggers, RLS policies, or computed columns.
What causes "PGRST116: More than one row returned by a subquery used as an expressi"?
A subquery in your SQL (trigger, RLS policy, or computed column) returns multiple rows when PostgreSQL expects exactly one Using = operator with a subquery that matches multiple records instead of using IN or ANY Missing or incorrect WHERE clause in the subquery that should narrow results to one row
Is the "PGRST116: More than one row returned by a subquery used as an expressi" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading