Postgres vs. SQL Server: Why Does My 'WHERE IN (list)' Query Fail with an Invalid Column Reference?

Postgres: "where in (list)" - Invalid Column Reference

In SQL Server, the following query would successfully delete rows from the user_job_titles table based on a list of IDs:

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

However, when attempting to execute this query in Postgres, an error occurs:

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

Cause:

The issue lies in the use of double quotes around the ID values in the IN clause. In Postgres, double quotes are reserved as escape characters for table and column names. By using them around the IDs, Postgres interprets them as references to non-existent columns, resulting in the error.

Solution:

To resolve this issue, replace the double quotes with 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'
                );

By using single quotes, the IDs are interpreted as string constants, allowing the IN clause to function correctly.

The above is the detailed content of Postgres vs. SQL Server: Why Does My 'WHERE IN (list)' Query Fail with an Invalid Column Reference?. For more information, please follow other related articles on the PHP Chinese website!