sql statement error: "column .. does not exist"

Question

Im trying from postgres console this command:

select sim.id as idsim, 
       num.id as idnum 
from main_sim sim 
  left join main_number num on (FK_Numbers_id=num.id);

and I've got this response:

ERROR:  column "fk_numbers_id" does not exist
LINE 1: ...m from main_sim sim left join main_number num on (FK_Numbers...

but if I simply check my table with:

dbMobile=# \d main_sim

 id              | integer               | not null default

 Iccid           | character varying(19) | not null

...

 FK_Device_id    | integer               | 

 FK_Numbers_id   | integer               | 

Indexes:
    "main_sim_pkey" PRIMARY KEY, btree (id)
    "main_sim_FK_Numbers_id_key" UNIQUE, btree ("FK_Numbers_id")
    "main_sim_Iccid_key" UNIQUE, btree ("Iccid")
    "main_sim_FK_Device_id" btree ("FK_Device_id")
Foreign-key constraints:
    "FK_Device_id_refs_id_480a73d1" FOREIGN KEY ("FK_Device_id") REFERENCES main_device(id) DEFERRABLE INITIALLY DEFERRED
    "FK_Numbers_id_refs_id_380cb036" FOREIGN KEY ("FK_Numbers_id") REFERENCES main_number(id) DEFERRABLE INITIALLY DEFERRED

...as we can see the column exist.

probably it's syntax error, but I'm unable to see what...

any help will'be appreciated. Alessio

Did you try sim.FK_Numbers_id instead of just FK_Numbers_id? — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Apr 4, 2012 at 16:42
Try typing FK_Numbers_id in quotes, like "FK_Numbers_id". As an advice: never use mized case in sql. — wildplasser
– wildplasser, Commented Apr 4, 2012 at 16:43

user330315 · Accepted Answer · 2012-04-04 16:43:39Z

41

No, the column FK_Numbers_id does not exist, only a column "FK_Numbers_id" exists

Apparently you created the table using double quotes and therefor all column names are now case-sensitive and you have to use double quotes all the time:

select sim.id as idsim, 
       num.id as idnum 
from main_sim sim 
   left join main_number num on ("FK_Numbers_id" = num.id);

To recap what is already documented in the manual:

The column foo and FOO are identical, the columns "foo" and "FOO" are not.

answered Apr 4, 2012 at 16:43

user330315

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

7 Comments

Conrad Frix Over a year ago

+1 I had trouble believing this was true because it seems insane but here's a sorta working demo. I had to alias the second column because sqlfiddle probably wasn't expecting the "same" name twice .

user330315 Over a year ago

@ConradFix: This is how the SQL standard is defined (with the small difference that the standard requires all names to be folded to uppercase)

user330315 Over a year ago

@AlessioTomelleri: you need to enclose the column name with double quotes as I showed in my answer. Those quotes make the difference!

Alessio Over a year ago

@wildplasser : yes, I think I'll do mine that advice

Alessio Over a year ago

@a_horse_with_no_name : it seems, you have got the problem... with quotes it's fine. To be honestly I missed told you about django. Django did the sql "create" statement directly and seems it created using quote... thanks for help

|

Collectives™ on Stack Overflow

sql statement error: "column .. does not exist"

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related