invalid input syntax for type uuid in PostgreSQL, measured

ERROR: invalid input syntax for type uuid: "undefined" means PostgreSQL was handed a piece of text to turn into a uuid and could not. The part in quotes is the text it received, exactly as received — which is the most useful thing in the message, because it usually tells you where the value came from. "" is an empty string, "undefined" and "null" are usually a missing value turned into a word — by an application, or by a JSON null cast through text — and a value with a space or a line break in the quotes was never trimmed. The SQLSTATE is 22P02.

We tried a few dozen inputs on PostgreSQL 17.11, through psql, a prepared statement, node-postgres 8.23.1 and a row-level-security policy, and wrote down what raised the error and what did not.

Which strings does PostgreSQL accept as a uuid?

More than the canonical form. These were cast without an error, and came back as the usual lowercase form with hyphens:

These raised invalid input syntax for type uuid: an empty string, undefined, null, NULL in quotes, 1, 0, id, a leading or trailing space, a trailing newline, a urn:uuid: prefix, 35 or 37 characters, a character outside 0–9 and a–f, a single brace, a hyphen after 5 digits, and a double hyphen. In every case the value in the message was the input, spaces and newline included, inside the quotes.

Where does “undefined” come from?

From the application, not from PostgreSQL. node-postgres sent a JavaScript null and undefined as SQL NULL: the query ran and returned 0 rows, no error. What failed was a string: 'undefined', `${id}` with id undefined, String(null), and ''. With a bind parameter, the server log showed the statement with $1 and not the value. That is the default: with log_parameter_max_length_on_error = -1 (PostgreSQL 13 and later) the log, and node-postgres’s err.where, also showed $1 = 'undefined'.

The error does not depend on the data. where id = 'undefined' against a uuid column failed on an empty table, and so did a plain EXPLAIN of the same query, and even PREPARE of it: a quoted value is converted when the statement is parsed, before a row is read. A prepared statement with a uuid parameter failed at EXECUTE for 'undefined' and '', and returned 0 rows for NULL. An array parameter, where id = any($1::uuid[]), failed when one element was '' or 'undefined', and worked with null elements.

The tempting workaround, where id::text = 'undefined', raised no error and returned 0 rows — and on a table of 100,000 rows it read all of them, because the cast on the column keeps the primary-key index out of play (an expression index on id::text would be used, but fixes the wrong thing). With a valid uuid compared as a uuid, the index was used. Fix the value, not the comparison.

Where does an empty string come from?

Three places in our tests. A form or API field that was sent but left empty. A JSON value: (body ->> 'id')::uuid failed for {"id": ""} and gave NULL for {"id": null} and for a missing key; (body -> 'id')::uuid, with a single arrow, failed differently, with cannot cast type jsonb to uuid. And a custom setting read with current_setting, which is where row-level security comes in.

Why does a row-level-security policy raise it?

A policy written as tenant_id = current_setting('app.tenant', true)::uuid works until the setting has been touched once in the session. As an ordinary role, with FORCE ROW LEVEL SECURITY:

current_setting(…, true) returns NULL for a setting the session has never seen, and an empty string once the setting has existed in it — also after a transaction-local value ended, after a rolled-back set_config(…, true) and after DISCARD ALL. A pooled connection is a session that has seen it. The error then appeared on every select and insert against the table, empty or not, and with no table or policy name in the message. An insert in those states raised the same error instead of the usual new row violates row-level security policy; with nullif, it was refused with the policy error. The full set of states, and how long a setting lasts in each form, is in current_setting and set_config in PostgreSQL.

How do you cast a value that may not be a uuid?

PostgreSQL 17 has no try_cast; try_cast('x' as uuid) is a syntax error. What we measured:

pg_input_is_valid and pg_input_error_info (both available since PostgreSQL 16) agreed with the cast on the thirteen inputs we checked, and pg_input_error_info(x, 'uuid') returned the message and SQLSTATE without raising anything — useful in an import, to list the bad rows instead of failing on the first. For the policy, nullif covers the empty string the setting falls back to; it does not cover an application that writes 'undefined' into it, so check the value before set_config. If an id comes from the client, reject a missing or malformed one before the query — a 400, or a 404 for a path segment. If it comes from your own code — a session, a token, a setting — the error is a bug to fix, not input to validate.

What we did not measure

Drivers and ORMs other than node-postgres, PostgreSQL versions other than 17.11, and the cost of the fixes. Other messages from the same row-level-security setup are in new row violates row-level security policy and, for the policy that returns nothing at all, debugging row-level security.

Sources: PostgreSQL 17 documentation on the uuid type and pg_input_is_valid, read on 16 October 2026. All measurements are our own, on PostgreSQL 17.11 and node-postgres 8.23.1, on 16 October 2026.