operator does not exist: uuid = text in PostgreSQL

ERROR: operator does not exist: uuid = text (SQLSTATE 42883) means a comparison has a uuid on one side and a value PostgreSQL already knows to be text on the other, and there is no = for that pair. There is no implicit cast between them: uuid to text was allowed on assignment (an insert of a uuid into a text column worked), text to uuid only explicitly. A quoted literal works (tenant_id = 'a0ee…'), because a literal has no type yet; a text column, a ::text cast, the result of current_setting() or a text parameter does not. The fix is an explicit cast — and which side you cast decides whether the index is used.

We measured each case on PostgreSQL 17.11, with psycopg2 2.9.13 and psycopg 3.3.6 for the driver cases. In a multi-tenant database the most common source is the tenant id from current_setting() in a row-level security policy.

Why does current_setting() give uuid = text?

Because current_setting() always returns text. On a table with tenant_id uuid:

select id from invoices where tenant_id = current_setting('app.tenant_id');
ERROR:  42883: operator does not exist: uuid = text
LINE 1: select id from invoices where tenant_id = current_setting('a...
                                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

In a policy it fails earlier: create policy p on invoices using (tenant_id = current_setting('app.tenant_id')) was refused at CREATE POLICY, with the same 42883 and without the LINE pointer. The cast is what makes it compile: tenant_id = current_setting('app.tenant_id', true)::uuid. It has four outcomes depending on the session:

The third row is the one that surprises, and the one a connection pool produces: once anything in the session has named the setting — a SET, a RESET, a set_config whose transaction has ended — it reads as an empty string, not NULL. Written as nullif(current_setting('app.tenant_id', true), '')::uuid, the policy gave 0 rows for a fresh session, 0 after a reset and the tenant’s rows once the setting was set. Why, and how to write the policy so an unset tenant gives no rows instead of an error, is in current_setting and set_config in PostgreSQL and invalid input syntax for type uuid.

Why does a literal work and a variable not?

The same applies to other pairs: integer = text, bigint = text, uuid = character varying, text = uuid and uuid = integer all gave 42883, and so did tenant_id like 'a0ee%' (uuid ~~ unknown): there is no LIKE for uuid.

Which side should you cast in a join?

Cast the text side to uuid, if every value in it is a valid uuid. On 100,000 rows with a btree index on invoices.tenant_id:

Casting the uuid column to text is the fix that always runs and never uses the index on that column. In a multi-tenant table where every query filters on tenant_id, that is the difference between an index lookup and a scan of every tenant’s rows. Clean up the legacy column, or give it a proper uuid type, rather than casting the indexed side.

Does the driver matter?

For a single value with %s, no; for lists, yes. psycopg2 interpolates parameters into the SQL text on the client, so a Python str arrives as a quoted literal of type unknown, and where tenant_id = %s worked. psycopg 3 binds on the server, and sends a str with type unknown too (OID 0), so the same line worked there as well, also with prepare=True, after automatic preparation and with prepare_threshold=0. Only the binary placeholder %b, or a parameter inside an expression such as coalesce(%s, …) (in both drivers), made it text and failed. The same mechanism on an insert gives a different message, column … is of type jsonb but expression is of type text; that one is in column is of type but expression is of type.

So code that filters a list of tenant ids can work on psycopg 3 and fail on psycopg2, unchanged. And the tenant id for set_config has to be passed as a string: the function takes text. How a transaction-scoped tenant setting behaves behind a pool is in PgBouncer and row-level security.

Functions and prepared statements

A SQL function f(p text) whose body said where tenant_id = p failed at CREATE FUNCTION with a $$ body, or at the call with check_function_bodies = off; with begin atomic it failed at CREATE regardless. A PL/pgSQL function was created and failed when called. prepare q(text) as … where tenant_id = $1 failed; prepare q(uuid) worked, and prepare q as … = $1 without a type inferred uuid and worked.

What we did not measure

Go, Node and Java drivers. ORMs that map uuid columns themselves. Performance of the expression index under writes.

All measurements are our own, on PostgreSQL 17.11 in containers, with psycopg2 2.9.13 and psycopg 3.3.6, on 22 October 2026.