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

Answer 1

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.