Postgres – Column “name” Does Not Exist

January 28, 2009

I don’t use Postgres very often, as I prefer MySQL, however a project I took over was built off Postgres and I find myself running into silly little road blocks. Today I encountered one that made me want to slap the developer when I finally realized what was going on. I created a simple insert query and tried to run it, and was presented with this error:

ERROR: column "-12.0" does not exist

The query I was trying to run was this:

INSERT INTO timezones (gmt_offset, "description")
("-12.0","(GMT -12:00) Eniwetok, Kwajalein"),
("-11.0","(GMT -11:00) Midway Island, Samoa"),
("-10.0","(GMT -10:00) Hawaii"),
("-9.0","(GMT -9:00) Alaska"),
("-8.0","(GMT -8:00) Pacific Time (US & Canada)"),
("-7.0","(GMT -7:00) Mountain Time (US & Canada)"),
("-6.0","(GMT -6:00) Central Time (US & Canada), Mexico City"),
("-5.0","(GMT -5:00) Eastern Time (US & Canada), Bogota, Lima"),
("-4.0","(GMT -4:00) Atlantic Time (Canada), Caracas, La Paz"),
("-3.5","(GMT -3:30) Newfoundland"),
("-3.0","(GMT -3:00) Brazil, Buenos Aires, Georgetown"),
("-2.0","(GMT -2:00) Mid-Atlantic"),
("-1.0","(GMT -1:00 hour) Azores, Cape Verde Islands"),
("0.0","(GMT) Western Europe Time, London, Lisbon, Casablanca"),
("1.0","(GMT +1:00 hour) Brussels, Copenhagen, Madrid, Paris"),
("2.0","(GMT +2:00) Kaliningrad, South Africa"),
("3.0","(GMT +3:00) Baghdad, Riyadh, Moscow, St. Petersburg"),
("3.5","(GMT +3:30) Tehran"),
("4.0","(GMT +4:00) Abu Dhabi, Muscat, Baku, Tbilisi"),
("4.5","(GMT +4:30) Kabul"),
("5.0","(GMT +5:00) Ekaterinburg, Islamabad, Karachi, Tashkent"),
("5.5","(GMT +5:30) Bombay, Calcutta, Madras, New Delhi"),
("5.75","(GMT +5:45) Kathmandu"),
("6.0","(GMT +6:00) Almaty, Dhaka, Colombo"),
("7.0","(GMT +7:00) Bangkok, Hanoi, Jakarta"),
("8.0","(GMT +8:00) Beijing, Perth, Singapore, Hong Kong"),
("9.0","(GMT +9:00) Tokyo, Seoul, Osaka, Sapporo, Yakutsk"),
("9.5","(GMT +9:30) Adelaide, Darwin"),
("10.0","(GMT +10:00) Eastern Australia, Guam, Vladivostok"),
("11.0","(GMT +11:00) Magadan, Solomon Islands, New Caledonia"),
("12.0","(GMT +12:00) Auckland, Wellington, Fiji, Kamchatka");

I found this to be very odd, as I’ve written queries just like that countless times.  So I tried tweaking the column types, I tried both text and numeric but just wasn’t getting anywhere.  Eventually, just on a whim, I replaced the double quotes with single quotes and it took the query.  For some reason Postgres interprets the query in a completely different way if you use double quotes.  How odd :S

Only registered users can comment.

  1. What a godsend — I’m in the exact situatino you describe, and the quotes were the problem for me. The error message I was getting said a column did not exist, and quoted the name of that column as a string value I’d passed in …!
    I really miss MySQL…

  2. I got the same error too,but if I tried to add another table , it is right .
    maybe it is a bug of postgresql。

    If I add an column in my first table in my database in postgresql, error message is : column not found ;if I delete all columns ,error message is : syntax error at or near “FROM” (what I do is just execute a sql statement:select * from )

  3. I got the same error and was wondering what the F*&k is going on. Are you serious, lack of double qoute support, now that already put’s me off postgres. MySQL is king

  4. OMG THAT’S EXACTLY MY PROBLEM, how I hate SQL, the error handling is just a joke
    THANK YOU SO MUCH you just saved my nerves from wrecking

  5. Does anybody know how to fix this error if you are importing an excel file?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.