CODEWITHSUNDEEP
sqlpostgresql
Saritha Ks
Saritha Ks

Reputation: 89

Can't fetch table values based on reference id of another table in psql

I have 3 tables like keyboard_countries, keyboard_languages and lang_regions. lang_regions contains the reference ids of keyboard_countries and keyboard_languages.

Schema for these tables are as follows:

create_table "keyboard_countries", force: true do |t|
t.string   "cc"
t.datetime "created_at"
t.datetime "updated_at"
end

create_table "keyboard_languages", force: true do |t|
t.string   "ln"
t.string   "lc"
t.string   "lr"
t.datetime "created_at"
t.datetime "updated_at"
end 

create_table "lang_regions", force: true do |t|
t.integer  "keyboardCountry_id"
t.integer  "keyboardLanguages_id"
t.datetime "created_at"
t.datetime "updated_at"
end



add_index "lang_regions", ["keyboardCountry_id"], 
name: "index_lang_regions_on_keyboardCountry_id", 
using: :btree add_index "lang_regions", 
["keyboardLanguages_id"], 
name: "index_lang_regions_on_keyboardLanguages_id", 
using: :btree

The query for fetching data that I have tried was: select keyboardCountry_id from lang_regions;

But I couldn't fetch data from lang_regions with the keyboardCountry_id:

The error was: column "keyboardlanguages_id" does not exist in lang_regions table.

Can anyone help me for finding a solution for this issue.

Upvotes: 0

Views: 137

Answers (1)

Timusan
Timusan

Reputation: 3445

As found in my comments on the question:

If you use identifiers (column names in your case) with all UPPERCASE characters or camelCasing you will have to double quote them in your queries to preserve the case sensitivity. If you do not double quote your identifiers, they will be folded to all lower case. Why? Because the SQL specification dictates it and PostgreSQL tries to adhere to it as much as possible.

Thus your original query:

select keyboardCountry_id from lang_regions;

Should become:

select "keyboardCountry_id" from lang_regions;

Another, more scalable approach might be to drop this camelCasing all together and rename your columns to be all lowercase. These will make for easier-to-read queries and avoids this pitfall all together (and makes your queries more SQL spec compliant).

Upvotes: 1

Related Questions