Postgresql IN statement

Question

Why is this query deleting all users when the user_id column does not exist at the profile table?

The initial query:

DELETE FROM users
    WHERE user_id IN (
        SELECT user_id FROM profile
        )
    AND user_id != 35
    AND user_id != 6;

To be more specific.

The IN statement query returns:

SELECT user_id FROM profile;

ERROR:  column "user_id" does not exist
LINE 1: SELECT user_id FROM profile;

Running the initial query returns

DELETE 100

Why is that happening?

Shouldn't it have stopped the execution upon error or return false or null?

Running PostgreSQL Server 9.0

possible duplicate of sql server 2008 management studio not checking the syntax of my query — Martin Smith
– Martin Smith, Commented May 6, 2013 at 9:20
This is correct behaviour. It is perfectly valid for a sub query to reference a column from the outer query. Use two part names if you want to avoid this kind of error. DELETE FROM users WHERE user_id IN (SELECT p.user_id FROM profile p) would give you an invalid column name error. — Martin Smith
– Martin Smith, Commented May 6, 2013 at 9:22
If however the table profile would contain 0 rows, then it wouldn't delete any rows from users. — Colin 't Hart
– Colin 't Hart, Commented May 6, 2013 at 9:46
@MartinSmith Would you be so kind as to answer the question so I can close it? — Jimmy Kane
– Jimmy Kane, Commented May 6, 2013 at 14:34

Martin Smith · Accepted Answer · 2013-05-06 20:05:14Z

8

This behavior is correct per ANSI standards.

If the unqualified column name doesn't resolve in the inner scope then the outer scope will be considered. So effectively you are doing an unintentional correlated sub query.

As long as the table profile contains at least one row then

 FROM users
 WHERE user_id IN (
        SELECT user_id FROM profile
        )

will end up matching all rows in users (except any where users.user_id IS NULL as WHERE NULL IN (NULL) does not evaluate to true). To avoid this possible issue you can use two part names.

DELETE FROM users
WHERE  user_id IN (SELECT p.user_id
                   FROM   profile p)

Would give the error

column p.user_id does not exist:

edited May 6, 2013 at 20:05

answered May 6, 2013 at 19:59

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jimmy Kane Over a year ago

Thanks again and for the detailed description. Appreciated!

Ben Gripka Over a year ago

Crazy, I was googling for the postgresql "in" statement behavior and this came up. By happenstance this was my issue. Thanks for the post.

Collectives™ on Stack Overflow

Postgresql IN statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related