Thread: Not able to find a table
I have a database that can be accessed and used correctly via ODBC and MS-Access. But when I try to do a simple SQL SELECT query in psql, I get an error that the table can't be found: Relation "instrument_type" does not exist. Yet, when I do a "\d" command, I see the table named "Instrument_Type" in the "public" schema. I use the database owner as the user id for psql. I need some help where to look in, say pgAdmin III (my tool of choice for database creation / maintenance) for a clue as to why I am getting this error. Thanks for any help, ideas, clues, starting points. Dale
Dale Seaburg wrote: > I have a database that can be accessed and used correctly via ODBC > and MS-Access. But when I try to do a simple SQL SELECT query in > psql, I get an error that the table can't be found: Relation > "instrument_type" does not exist. Yet, when I do a "\d" command, I > see the table named "Instrument_Type" in the "public" schema. I use > the database owner as the user id for psql. > > I need some help where to look in, say pgAdmin III (my tool of choice > for database creation / maintenance) for a clue as to why I am > getting this error. > > Thanks for any help, ideas, clues, starting points. > Try doing: select * from "Instrument_Type" limit 10; in psql. The problem is that for unquoted table names, postgres automatically lower-cases everything. To access tables with upper case letters in their names, you need to quote them (using " quotes). Brian
On Mon, Jul 30, 2007 at 09:26:58AM -0500, Dale Seaburg wrote: > I have a database that can be accessed and used correctly via ODBC > and MS-Access. But when I try to do a simple SQL SELECT query in > psql, I get an error that the table can't be found: Relation > "instrument_type" does not exist. Yet, when I do a "\d" command, I > see the table named "Instrument_Type" in the "public" schema. I use > the database owner as the user id for psql. See "Why are my table and column names not recognized in my query? Why is capitalization not preserved?" in the FAQ and "Identifiers and Key Words" in the documentation, in particular the part that discusses quoted identifiers and case sensitivity: http://www.postgresql.org/docs/faqs.FAQ.html#item4.21 http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr
On Jul 30, 2007, at 9:26 , Dale Seaburg wrote: > I have a database that can be accessed and used correctly via ODBC > and MS-Access. But when I try to do a simple SQL SELECT query in > psql, I get an error that the table can't be found: Relation > "instrument_type" does not exist. Yet, when I do a "\d" command, I > see the table named "Instrument_Type" in the "public" schema. SQL is case-insensitive unless the identifier is quoted. PostgreSQL downcases unquoted identifiers, so instrument_type InSTrument_type INSTRUMENT_TYPE Instrument_Type all are treated as instrument_type. Note that \d returns "Instrument_Type". This means the identifier was quoted when created. "Instrument_Type" is not the same as Instrument_Type or instrument_type. You need to quote the identifier to use it, e.g., SELECT * FROM "Instrument_Type"; Michael Glaesemann grzm seespotcode net
A big thanks to Brian Hurt, Michael Fuhr and Michael Glaesemann for pointing out the need to quote case-sensitive table and column names. Because I tend to search the documentation when I run into a problem, rather than go through the docs thoroughly (all good programmers *always* read the docs fully first , right? LOL), I was not aware of the quote need. Thanks again for the help. I am transitioning from MS-Access to C# and Npgsql. What a challenge, to say the least! Someone could make a ton of money writing an Access-to-C# converter program... LOL! Dale. On Jul 30, 2007, at 9:26 AM, Dale Seaburg wrote: > I have a database that can be accessed and used correctly via ODBC > and MS-Access. But when I try to do a simple SQL SELECT query in > psql, I get an error that the table can't be found: Relation > "instrument_type" does not exist. Yet, when I do a "\d" command, I > see the table named "Instrument_Type" in the "public" schema. I > use the database owner as the user id for psql. > > I need some help where to look in, say pgAdmin III (my tool of > choice for database creation / maintenance) for a clue as to why I > am getting this error. > > Thanks for any help, ideas, clues, starting points. > > Dale > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org