Why Does My PostgreSQL DELETE Query Fail with 'Column Does Not Exist' When Using IN (List)?

Postgres: Query Error "Column Does Not Exist" with WHERE IN (List)

In PostgreSQL, the query

DELETE FROM user_job_titles WHERE id IN (
"c836d018-1d12-4507-a268-a4d80d6d3f54",
"d0961a90-7d31-4c4c-9c1b-671115e3d833",
"62dda420-6e62-4017-b41d-205c0aa82ead"
)

fails with an "error: column [value] does not exist" error. This is because:

Double Quotes and Escaping

In PostgreSQL, double quotes are used as escape characters to refer to table and column names. When used inadvertently with string literals, they can lead to incorrect syntax. In the given query, the values in the IN list are enclosed in double quotes, causing PostgreSQL to interpret them as column names rather than string constants.

Solution: Enclose Values in Single Quotes

To resolve the issue, enclose the values in single quotes:

DELETE FROM user_job_titles WHERE id IN (
'c836d018-1d12-4507-a268-a4d80d6d3f54',
'd0961a90-7d31-4c4c-9c1b-671115e3d833',
'62dda420-6e62-4017-b41d-205c0aa82ead'
);

This way, PostgreSQL interprets the values as string constants and compares them correctly with the id column of the user_job_titles table.

The above is the detailed content of Why Does My PostgreSQL DELETE Query Fail with 'Column Does Not Exist' When Using IN (List)?. For more information, please follow other related articles on the PHP Chinese website!