Given:

orig_sav=# \d realtycompany_contacts
Table "public.realtycompany_contacts"
Column | Type | Modifiers
------------------+--------+-----------
realtycompany_id | bigint | not null
contact_id | bigint | not null


and

orig_sav=# \d users
Table "public.users"
Column | Type | Modifiers
-------------------+-----------------------------+-----------
id | bigint | not null
name | text |
password | text | not null
version | integer | not null
contact | bigint |
comment | text |
organization | bigint |
pwd_storage_style | integer |
old_name | text |
deleted | boolean | not null
deleted_date | timestamp without time zone |


Why in the world does this statement parse and run:

orig_sav=# delete from realtycompany_contacts where contact_id in
(select contact_id from users);
DELETE 1634

Since users has *no* column contact_id ? One would expect the statement
to fail, and the transaction to get rolled back.


orig_sav=# select contact_id from users;
ERROR: column "contact_id" does not exist

This is on 7.4.2.


----
James Robinson
Socialserve.com


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Aha. Well, you learn something new every day. Yes, the behavior is
indeed like "contact_id is not null", which was true for all rows in
users, which explains why I lost all my data in realtycompany_contacts.

Thank goodness for backups. Many thanks!

James

On Jul 1, 2004, at 3:35 PM, Stephan Szabo wrote:

James Robinson
Socialserve.com


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Thu, 1 Jul 2004, James Robinson wrote:

AFAIK the spec requires that subselects like that can reference outer
columns so contact_id inside the subselect refers to the outer contact_id
column which does exist (which makes the effective behavior of the above
clause the same as contact_id is not null I think)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly