Why Does My PostgreSQL `WHERE IN` Clause Cause a 'column does not exist' Error?

PostgreSQL: "column does not exist" Error in WHERE IN (list) Query

When attempting to delete rows from the user_job_titles table in PostgreSQL using a WHERE IN (list) clause, users may encounter the following error:

ERROR:  column "c836d018-1d12-4507-a268-a4d80d6d3f54" does not exist
LINE 2: "c836d018-1d12-4507-a268-a4d80d6d3f54"

This occurs when attempting to use double quotes (") within the WHERE IN clause, as demonstrated in the following query:

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

The solution to this error is to use single quotes (') around the string constants in the WHERE IN clause. Double quotes are used as escape characters for table and column names in PostgreSQL, thus they cannot be used to enclose string literals.

Therefore, the correct query should be:

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

The above is the detailed content of Why Does My PostgreSQL `WHERE IN` Clause Cause a 'column does not exist' Error?. For more information, please follow other related articles on the PHP Chinese website!