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.
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.
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:
// 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 expressionIdentify which SQL operation (INSERT, UPDATE, or trigger) is causing the issue.
If your subquery is expected to return multiple rows, use the IN operator instead of =:
-- 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:
WHERE column IN (SELECT column FROM table)If the subquery should only return one row, explicitly limit it:
-- 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.
If you need a calculated single value from multiple rows, use aggregate functions:
-- 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);The most common fix: ensure your subquery's WHERE clause uniquely identifies one row:
-- 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.
If the error occurs with RLS policies, review your policy definitions:
-- 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));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
Correlated Subqueries: When using correlated subqueries in UPDATE statements, ensure the correlation properly links each row to exactly one result:
-- 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:
-- 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:
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;email_address_not_authorized: Email sending to this address is not authorized
Email address not authorized for sending in Supabase Auth
reauthentication_needed: Reauthentication required for security-sensitive actions
Reauthentication required for security-sensitive actions
no_authorization: No authorization header was provided
How to fix "no authorization header was provided" in Supabase
otp_expired: OTP has expired
How to fix 'otp_expired: OTP has expired' in Supabase
bad_oauth_state: OAuth state parameter is missing or invalid
How to fix 'bad_oauth_state: OAuth state parameter missing' in Supabase