Postgres: invalid input syntax for type uuid
An empty filter or a legacy id reaches a uuid parameter. Treat absent as no filter and validate before the query.

Introduction & Quick Summary
A query that worked in development fails in production with:
ERROR: invalid input syntax for type uuid: "" (SQLSTATE 22P02)
or the same error carrying a value that is obviously not a UUID, such as "42" or "abc". The column is uuid, the parameter is text, and PostgreSQL refuses to convert it. 22P02 is a data-exception class error, raised during input conversion, before any index or constraint is consulted.
The value in the message is the whole clue. In practice there are four sources:
- An empty string arrives where a UUID is expected. A URL filter such as
?id=eq.or.eq("id", "")from a client that did not validate its input produces exactly"". - A non-UUID identifier is used. A numeric legacy id, a slug, or a prefixed id like
usr_1a2bcannot be cast touuid. - A short or malformed UUID. Missing characters, stray spaces, or a value pasted with quotes around it.
- A
NULLthat became''on the way. Some ORMs and CSV loaders convert empty CSV fields to empty strings, which then fail on insert.
The fix belongs at two levels: make the input valid or explicitly absent, and make the query treat "no value" as no filter rather than as an empty string.
Core Mechanics, Signatures & Reproducible Setup
uuid is a 16-byte fixed-width type. Its input function accepts the standard hyphenated form, a form without hyphens, braces around the value, and uppercase hex; it rejects empty strings, whitespace-only strings, and any length that is not 32 hex digits once separators are stripped.
select '550e8400-e29b-41d4-a716-446655440000'::uuid; -- ok
select '550E8400E29B41D4A716446655440000'::uuid; -- ok, no hyphens
select '{550e8400-e29b-41d4-a716-446655440000}'::uuid; -- ok, braces
select ''::uuid; -- ERROR: invalid input syntax for type uuid: ""
select '550e8400'::uuid; -- ERROR, too short
select ' 550e8400-e29b-41d4-a716-446655440000 '::uuid; -- ok, surrounding spaces trimmedReproduce it with a table and two queries, one of which will fail:
create table users (id uuid primary key default gen_random_uuid(), email text);
insert into users (email) values ('a@example.com');
select * from users where id = ''; -- 22P02
select * from users where id = null; -- no rows, no error
select * from users where id = 'not-a-uuid'; -- 22P02PostgREST and Supabase clients translate query parameters into exactly these predicates, which is why a missing filter value becomes an empty string in the SQL and not a NULL.
Key Comparison Table
| Incoming value | Cast result | Recommended handling |
|---|---|---|
"" | error 22P02 | treat as absent, do not filter |
NULL | null | IS NULL comparison |
"42" | error 22P02 | reject in validation, or look up by another column |
| valid 36-char UUID | uuid | normal case |
| 32 hex chars | uuid | accepted, normalise for display |
| leading or trailing spaces | uuid | trimmed, but normalise anyway |
| uppercase | uuid | accepted, stored lowercase |
| Layer | Correct behaviour | Common bug |
|---|---|---|
| Client form | empty input means "no filter" | sends "" as a filter value |
| API handler | validate with a UUID parser | passes the raw string through |
| PostgREST | omit empty filters | ?id=eq. in a generated URL |
| SQL | NULLIF($1,'')::uuid for optional params | $1::uuid on every call |
| Insert path | default gen_random_uuid() | explicit '' overriding the default |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. SQL that accepts an optional UUID
When a parameter is optional, the cast has to happen after the empty string is normalised. NULLIF turns '' into NULL, and a null comparison then means "no filter":
select *
from orders
where ($1::text is null or $1::text = '')
or customer_id = nullif($1::text, '')::uuid;A cleaner shape separates the two cases explicitly, which also keeps the planner from choosing a bad plan for the "no filter" branch:
select * from orders where customer_id = $1::uuid; -- required param
select * from orders; -- no paramGuarding with nullif inside a single query is fine for an admin endpoint; for a hot path, build the query in the application and omit the predicate.
2. Supabase and PostgREST: the empty filter
supabase.from("orders").eq("id", id) where id is "" produces id=eq. and the error above. .single() then reports PGRST116 on top of it, which hides the real cause. Validate before the call and use maybeSingle when zero rows is a legitimate outcome:
import { z } from "zod";
const Id = z.string().uuid();
export async function getOrder(id: string) {
const parsed = Id.safeParse(id);
if (!parsed.success) return null; // invalid input, no query
const { data, error } = await supabase
.from("orders")
.select("id, total, created_at")
.eq("id", parsed.data)
.maybeSingle();
if (error) throw error;
return data;
}For a list endpoint with an optional filter, omit the .eq() call entirely rather than passing an empty value:
let q = supabase.from("orders").select("id, total");
if (customerId) q = q.eq("customer_id", customerId);3. Inserts that override the default
A uuid column with default gen_random_uuid() still fails if the insert explicitly passes ''. CSV imports and ORMs that send every column are the usual path:
insert into users (id, email) values ('', 'a@example.com'); -- 22P02
insert into users (email) values ('a@example.com'); -- default appliesWhen importing, map empty CSV fields to NULL rather than to an empty string, or omit the column. In SQL, nullif(:id, '') inside the values clause does the same job.
4. The uuid/text comparison that looks like the same bug
A different error appears when a text column holds UUID-looking values: operator does not exist: uuid = text (SQLSTATE 42883). It is not a broken value, it is a missing cast, and adding one changes index usage:
-- slow: cast the column, index on id is unusable
select * from events where id::text = '550e8400-e29b-41d4-a716-446655440000';
-- fast: compare in the column's own type
select * from events where id = '550e8400-e29b-41d4-a716-446655440000'::uuid;auth.uid() returns uuid, so an RLS policy written as auth.uid() = user_id::text is both slower and easier to get wrong.
5. Generating and validating UUIDs without guessing
PostgreSQL 13 and later have gen_random_uuid() built in; before that it came from pgcrypto. Inserting a value produced by the database is always valid, which removes the class of error from the write path:
select gen_random_uuid();
create extension if not exists pgcrypto; -- only needed before PG 13To check a value by hand, generate or validate one in UUID Generator: paste the string from the error message and see whether it is a UUID at all, which separates "empty from the client" from "wrong id from a legacy system" in seconds.
6. Finding every path that can send an empty value
The error usually appears in one endpoint while the same mistake exists in five. Search the codebase for the pattern rather than fixing the reported route:
grep -rn "eq(\"id\"\|eq('id'\|id=eq\." src | head -20
grep -rn "::uuid" migrations | head -20In SQL logs, 22P02 shows the offending literal; enable log_min_error_statement = error so the statement, not just the message, is captured. That is the fastest way to see whether the '' came from a filter, an insert, or a default that was overridden.
Common Compilation/Runtime Mistakes & Version Gotchas
- Catching the error and returning "not found". A malformed id then looks like a missing record, which hides bugs and can mask a bad client.
coalesce($1, '')in the other direction. TurningNULLinto''creates this error; normalise the empty string toNULL, never the reverse.- Casting the column instead of the parameter.
id::text = $1works and disables the index; cast the parameter. - Assuming a UUID column accepts anything unique. It accepts one format family; ids like
usr_...needtextor a prefixed composite type. - ORMs that store UUIDs as
varchar. The comparison then happens as text, and a mixed schema has both behaviours depending on the table. - Empty JSON fields.
{"id": ""}becomes''when passed to a typed parameter; a JSONnullbecomes SQLNULL. The two are not interchangeable. - A client library that trims whitespace but not empty strings. A value of
" "may pass validation after trimming and then fail the cast.
Primary references: the UUID type documents the accepted input forms, and the error codes appendix places 22P02 in class 22, data exception.
Official references
primary documentationThe SQL documentation that defines the behaviour this guide explains.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQLite: database is locked (SQLITE_BUSY) under concurrent writes
SQLite allows one writer. Set a busy timeout, switch to WAL, start write transactions as IMMEDIATE, and reduce to a single writer connection.
MySQL: ERROR 1040 (HY000) Too many connections
The server is out of slots because pools are too large or leaking. Read the counters, size pools against replicas, then raise the limit.
MySQL 8: Public Key Retrieval is not allowed over JDBC
The driver never sent the password. Use verified TLS, or allow key retrieval only where the network is trusted.
MySQL: Lock wait timeout exceeded, and how to find the blocker
The waiter is the symptom. Find the blocker, shorten the transaction that holds locks, and stop raising the timeout as a fix.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.