Thread: "field doesn't exist" even though I'm sure it does
Hey everybody. I've got a strange one today. I'm trying to convert an extremely messy access sql query into something that can be used with our postgresql database (the pgsql db is being built to replace the access db). I had barely begun trying to convert it when I was confronted with this error message: column "isactive" of relation "membermailingaddress" does not exist However, a quick \d in psql let's me know that there is indeed a field "isactive" on that specific table. I can't begin to imagine why it would throw an error like this. Here's my psql output, with a simple test query to reproduce the issue, and a \d membermailingaddress afterwards. Anybody have any ideas? current=# insert into membermailingaddress( current(# mb_cn_num, current(# mb_acct_num, current(# IsActive, current(# IsMember current(# ) current-# VALUES current-# ( current(# 1, current(# 1500.0, current(# TRUE, current(# FALSE current(# ) current-# ; ERROR: column "isactive" of relation "membermailingaddress" does not exist LINE 4: IsActive, ^ current=# \d membermailingaddress Table "public.membermailingaddress" Column | Type | Modifiers -----------------+--------------+----------- mb_cn_num | integer | mb_acct_num | numeric(6,1) | IsActive | boolean | IsMember | boolean | IsMail | boolean | IsPromoMail | boolean | HouseholdID | numeric | HouseholdSize | integer | Name1 | text | Name2 | text | Addr1 | text | Addr2 | text | Addr3 | text | City | text | State | text | Zip | text | Zip5 | text | xxPrimaryName | text | xxJointName | text | xxHouseholdHash | text | current=# TIA Thomas R. Hart II tomhart@coopfed.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 16 Nov 2007 15:13:48 -0500 Tom Hart <tomhart@coopfed.org> wrote: > Addr2 | text | > Addr3 | text | > City | text | > State | text | > Zip | text | > Zip5 | text | > xxPrimaryName | text | > xxJointName | text | > xxHouseholdHash | text | > > current=# > > TIA > It is because when you created the relation you double quoted isactive, so it isn't isactive it is "isActive". E.g; case is relevant. Joshua D. Drake > Thomas R. Hart II > tomhart@coopfed.org > > ---------------------------(end of > broadcast)--------------------------- TIP 9: In versions below 8.0, > the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHPfuVATb/zqfZUUQRAv0YAJ9C2YwfiJILDj3DQYmZoCVFBjQ9QwCfZeGE 22+aBWTnM0FYpuBjJJGBSuc= =5CnL -----END PGP SIGNATURE-----
On Fri, Nov 16, 2007 at 03:13:48PM -0500, Tom Hart wrote: > column "isactive" of relation "membermailingaddress" does not exist > > Table "public.membermailingaddress" > Column | Type | Modifiers > -----------------+--------------+----------- > IsActive | boolean | If you don't enclose an identifier in double quotes (") then PG will make the identifier lowercase before looking for it. This is in an attempt to support case-insensitive general usage, while still allowing case-sensitive usage if you really want it. Sam
Sam Mason wrote: > On Fri, Nov 16, 2007 at 03:13:48PM -0500, Tom Hart wrote: > >> column "isactive" of relation "membermailingaddress" does not exist >> >> Table "public.membermailingaddress" >> Column | Type | Modifiers >> -----------------+--------------+----------- >> IsActive | boolean | >> > > If you don't enclose an identifier in double quotes (") then PG will > make the identifier lowercase before looking for it. This is in an > attempt to support case-insensitive general usage, while still allowing > case-sensitive usage if you really want it. > Ok, that makes sense. Like I said I created this db to mimic our previous db, but I think the best solution here would be to continue my trend of using all lowercase column names so that I'm not faced with this problem. Thank you for your reply, and you too Joshua. Sorry for such an easy question :-) > Sam > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 16 Nov 2007 15:25:30 -0500 Tom Hart <tomhart@coopfed.org> wrote: > Ok, that makes sense. Like I said I created this db to mimic our > previous db, but I think the best solution here would be to continue > my trend of using all lowercase column names so that I'm not faced > with this problem. > > Thank you for your reply, and you too Joshua. Sorry for such an easy > question :-) No sweat :)... I trust you will have more. Bring them on. Joshua D. Drake > > Sam > > > > ---------------------------(end of > > broadcast)--------------------------- TIP 6: explain analyze is > > your friend > > > ---------------------------(end of > broadcast)--------------------------- TIP 1: if posting/reading > through Usenet, please send an appropriate subscribe-nomail command > to majordomo@postgresql.org so that your message can get through to > the mailing list cleanly > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHPf4WATb/zqfZUUQRAntLAJ4s6L7sLvYkeMkwGPfaKu/GuK+gGgCfXXd5 0/qLZJQUZd3TMEk6/vptgls= =uYbF -----END PGP SIGNATURE-----