Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#PostgreSQL#Supabase#SQL#Validation
A uuid column slot receiving an empty text plate blocked by an amber 22P02 plate

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:

  1. 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 "".
  2. A non-UUID identifier is used. A numeric legacy id, a slug, or a prefixed id like usr_1a2b cannot be cast to uuid.
  3. A short or malformed UUID. Missing characters, stray spaces, or a value pasted with quotes around it.
  4. A NULL that 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 trimmed

Reproduce 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'; -- 22P02

PostgREST 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 valueCast resultRecommended handling
""error 22P02treat as absent, do not filter
NULLnullIS NULL comparison
"42"error 22P02reject in validation, or look up by another column
valid 36-char UUIDuuidnormal case
32 hex charsuuidaccepted, normalise for display
leading or trailing spacesuuidtrimmed, but normalise anyway
uppercaseuuidaccepted, stored lowercase
LayerCorrect behaviourCommon bug
Client formempty input means "no filter"sends "" as a filter value
API handlervalidate with a UUID parserpasses the raw string through
PostgRESTomit empty filters?id=eq. in a generated URL
SQLNULLIF($1,'')::uuid for optional params$1::uuid on every call
Insert pathdefault 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 param

Guarding 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:

ts
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:

ts
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 applies

When 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 13

To 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:

bash
grep -rn "eq(\"id\"\|eq('id'\|id=eq\." src | head -20 grep -rn "::uuid" migrations | head -20

In 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. Turning NULL into '' creates this error; normalise the empty string to NULL, never the reverse.
  • Casting the column instead of the parameter. id::text = $1 works and disables the index; cast the parameter.
  • Assuming a UUID column accepts anything unique. It accepts one format family; ids like usr_... need text or 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 JSON null becomes SQL NULL. 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 documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.