Why would an ID be text in the first place?

External IDs, invoice numbers, Shopify-style IDs, UUIDs, and anything that can have a leading zero or a prefix (INV-1042) are stored as text. Auto-increment primary keys are usually integers. describe_table tells you which one you have.

What does Postgres do with WHERE id = 123 if id is text?

It errors: operator does not exist: text = integer (or a similar type mismatch). That's the loud, useful failure. The AI has to retry with quotes.

It often coerces. WHERE id = 123 against a VARCHAR id can match '123' and sometimes surprise you with '123abc' depending on context — or miss '00123' because 00123 as a number is 123, and '00123' as text is not '123'.

Is this the same as case-sensitive search?

No. Case sensitivity is about Active vs active. This is about type: the literal 123 is not the string '123', and '00123' is not 123. Related family of 'same filter, different database' bugs, different mechanism.

What's the safe habit?

describe_table first. If the column is text/varchar/uuid, quote the literal and don't strip leading zeros. If it's an integer, don't quote it and don't send 00123 expecting that padding to survive.

Text ID vs Integer Filter | Implicit Cast

You ask for "customer 00123." The AI writes WHERE id = 123 — or WHERE id = '123'. One of those finds the row. The other finds nothing, or blows up, depending on what type id actually is and which database is answering.

The ID is not always a number

Plenty of real id columns are TEXT / VARCHAR:

invoice numbers (INV-1042)
external IDs from Stripe, Shopify, a legacy system
zero-padded codes (00123)
UUIDs stored as text

describe_table shows this. The model often skips it and treats "id" like an integer because that's what training data taught it IDs look like — the same skip that invents column names.

Same filter, three databases

PostgreSQL is strict. WHERE id = 123 against a text column is a type error: there is no text = integer operator. Loud, easy to notice, one retry with quotes.

MySQL is loose. It often casts. WHERE id = 123 against '00123' can miss the row (numeric 123 ≠ text 00123) or, in other comparisons, match extra rows because '123abc' casts toward 123. Silent, easy to ship.

SQL Server is in the middle — it will convert when it can, and the conversion of '00123' to int is 123, which is not the same lookup as the string '00123'.

So "just drop the leading zeros, IDs are numbers" is a Postgres error, a MySQL miss, or a SQL Server miss — three failure modes from one assumption.

Leading zeros are the tell

If your admin UI shows 00123 and the query used 123, you are already in this bug. Padding is data. Casting to integer deletes it. The fix is not smarter math; it's matching the literal to the column type:

-- text / varchar id
WHERE id = '00123'

-- integer id
WHERE id = 123

Never both. Never "try the other one if the first is empty" without looking at the type — that second attempt is how you match the wrong row.

How to catch it

If a lookup that "must exist" comes back empty or errors on a type mismatch:

describe_table — is id integer, text, or uuid?
SELECT id FROM customers WHERE id::text LIKE '%123%' (or the MySQL/SQL Server equivalent) to see the stored form
Re-run the filter with that exact stored value, quoted if the column is text

The prompt that heads this off

Before filtering on id, customer_id, or any *id column, describe_table
and match the literal to the type. Text/varchar/uuid: quote it, keep
leading zeros and prefixes. Integer: unquoted number, no padding.
Don't cast a text id to int to "clean it up."

The short version

00123 and 123 are the same integer and different strings. Which comparison you wanted is a column-type question, not a style question — and Postgres, MySQL, and SQL Server will not all tell you when you guessed wrong.

Read-only MCP gateway: mcpserver.design.

Related reading: