From: | Oliver Dizon <johnoliver(at)rtzassociates(dot)com> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: (unknown) |
Date: | 2015-01-28 02:07:51 |
Message-ID: | 212464190.233193.1422410871659.JavaMail.root@mail |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Oliver Dizon schrieb am 27.01.2015 um 11:46:
> I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.
>
> All records were deleted in a table even if the subquery in the where clause has a missing a column.
>
> --from a certain script where table_id is not yet existing on table_sample
>
> delete from main_table_sample where table_id in (select table_id from table_sample);
>
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
>
> But it did not returned any error and deleted all the records instead.
>
> Version: psql (8.4.9, server 9.2.5)
>No, this is correct and complies with the SQL standard. If you reference a column in a sub-select that is available in the outer query but not in the sub-select, then it's automatically a reference to the column from the outer query.
>So in your query, the column "table_id" in the sub-query references the main_table_sample.table_id.
>I agree this is confusing, but it's the way it was specified in the SQL standard.
>Thomas
Yea. I've realized that it's yet another example of "outer
reference". I guess I really need to read another SQL book again :-)
Thanks!
______________________
Oliver G. Dizon
Z Getcare Systems Team
RTZ Associates, Inc.
johnoliver(at)rtzassociates(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Mackler | 2015-01-28 04:06:30 | Conflicting function name in dynamically-loaded shared library |
Previous Message | Oliver Dizon | 2015-01-28 02:03:14 | Re: |