Fix PostgreSQL invalid input syntax for type uuid

PostgreSQL's invalid input syntax for type uuid error usually means an application passed a value that looked harmless in code but could not be parsed as a UUID by the database. The most common cases are empty strings from forms, route parameters such as undefined, legacy IDs that are not UUIDs, and text values copied with extra characters.

The fix is not to loosen the column type. Keep the column as uuid, validate input at the application boundary, and only cast text to UUID after you know it is present and well formed. This quick fix shows how to find the bad value, repair the query path, and avoid turning a one-row request bug into a production incident.

The error and why it happens

A typical failing query looks like this:

select *
from accounts
where id = $1::uuid;

If the parameter is an empty string, abc, undefined, or a comma-separated value, PostgreSQL cannot convert it to the uuid type and raises an invalid text representation error. In PostgreSQL's SQLSTATE appendix, invalid text representation is 22P02, which is why logs and drivers often include both the human message and SQLSTATE 22P02.

PostgreSQL's UUID type accepts the standard 8-4-4-4-12 hexadecimal form and a few documented variants such as uppercase characters, braces, omitted hyphens, or extra hyphens after groups of four digits. That flexibility does not mean any string can become a UUID. Empty strings are still empty strings, placeholder words are still text, and malformed route parameters still fail before the query can use an index.

Diagnose the exact input first

Start by identifying the value sent to PostgreSQL, not by changing the table. In an API route, log the request identifier, the route, and whether the ID parameter is missing, empty, or malformed. Avoid logging sensitive payloads; for IDs, it is usually enough to log the raw parameter length and a safely redacted sample.

In SQL, reproduce the failure with the smallest cast possible:

select ''::uuid;
select 'undefined'::uuid;
select '550e8400-e29b-41d4-a716-446655440000'::uuid;

The first two statements should fail and the third should succeed. That confirms the problem is input conversion rather than permissions, table visibility, or a missing row.

Fix the application boundary

The clean fix is to reject invalid values before they reach the query. For example, a web API can check that the route parameter exists and matches the UUID shape before executing SQL. This is not a replacement for authorization, and it is not a security boundary by itself; it is a way to keep obvious bad input out of the database path and return a useful client error.

const uuidPattern = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

if (!id || !uuidPattern.test(id)) {
  return new Response("Invalid account id", { status: 400 });
}

await db.query("select * from accounts where id = $1::uuid", [id]);

If your API accepts optional UUIDs, do not send an empty string and hope PostgreSQL treats it as NULL. It will not. Convert blank input into null in application code, then write SQL that handles NULL intentionally:

insert into invitations (account_id, email)
values ($1::uuid, $2);

In this example, the first parameter should be either a valid UUID string or a real SQL null value from the driver. It should not be ''.

When the value comes from SQL text

Sometimes the bad cast is inside SQL rather than in application parameters. A common pattern is joining a text column that was used during an import to a real UUID column:

select *
from imported_events e
join accounts a on a.id = e.account_id_text::uuid;

That query fails as soon as one imported row contains bad text. Before casting in bulk, isolate the bad rows. PostgreSQL's UUID documentation describes the accepted input forms, and for simple cleanup you can use string checks to find obvious blanks before you attempt a stricter migration.

select account_id_text
from imported_events
where account_id_text is null
   or btrim(account_id_text) = '';

For a production migration, prefer a staged cleanup: add a new UUID column, backfill only rows that pass validation, review rejected rows, then switch the application to the typed column. Casting a whole dirty text column in one statement is convenient until one malformed value aborts the migration.

Keep the UUID type

A tempting workaround is to compare everything as text: where id::text = $1. That avoids the cast error, but it pushes the problem into performance and correctness. Casting the column can prevent normal index usage in common plans, hides invalid client behavior, and makes it easier for mixed identifier formats to leak through the system.

The better production pattern is boring: use UUID columns for UUID data, pass parameters instead of string-concatenated SQL, validate route and form values before the database call, and keep optional values as real nulls. On a managed PostgreSQL service such as ArmorDB, this also keeps errors easier to triage because the database logs point to the endpoint that sent malformed input rather than to a schema workaround.

Quick checklist

Use this order when the error appears in production. First, capture the failing route or job and the raw ID shape. Next, reproduce the cast with select $value::uuid in a safe session. Then fix the boundary that produced the malformed value, add a regression test for blank and placeholder IDs, and only then inspect old data if the error came from a migration or import.

If the value is user supplied, return a 400-level response. If it is produced by your own service, treat it as a bug in request construction. If it comes from a data migration, stop and clean the source rows before retrying the cast.

Takeaway

invalid input syntax for type uuid is usually an input hygiene problem, not a PostgreSQL problem. Keep the strong uuid type, reject malformed strings early, convert blanks to real nulls when the field is optional, and handle imported text with a staged cleanup instead of blind casts.

Sources and further reading

• PostgreSQL UUID type documentation: https://www.postgresql.org/docs/current/datatype-uuid.html
• PostgreSQL UUID functions: https://www.postgresql.org/docs/current/functions-uuid.html
• PostgreSQL error codes appendix: https://www.postgresql.org/docs/current/errcodes-appendix.html
• PostgreSQL string functions, including btrim: https://www.postgresql.org/docs/current/functions-string.html