Thread: Cannot open table in new database
We've set up a Sql database for the first time and get an error reported
back to our application from the ODBC session object when we try to open
one of the tables.
[42p01][7]ERROR Relation "SqlAnal" does not exist; table not found!
Here is my database outline in the Admin tool
Servers(1)
Concept Patterns (localhost:5432)
Databases(1)
Postgres
casts
languages
schemas
Public
-
-
Tables(2)
DocHeader
SqlAnal
Replication
Tablespaces(2)
pg_default
pg_global
Group Roles
Login Roles(1)
Concept
We can access the list of tables from the ODBC driver which shows the
above tables.
We've obviously done something wrong but we don't know where to look.
The Help button is not helpful at all.
Any ideas on what we are doing incorrectly?
Cheers
Phil Jackson
On 07/01/2010 09:46 AM, Phil Jackson wrote: > We've set up a Sql database for the first time and get an error reported > back to our application from the ODBC session object when we try to open > one of the tables. > > [42p01][7]ERROR Relation "SqlAnal" does not exist; table not found! > > Here is my database outline in the Admin tool > > Servers(1) > Concept Patterns (localhost:5432) > Databases(1) > Postgres > casts > languages > schemas > Public > - > - > Tables(2) > DocHeader > SqlAnal > Replication > Tablespaces(2) > pg_default > pg_global > Group Roles > Login Roles(1) > Concept > > We can access the list of tables from the ODBC driver which shows the > above tables. > > We've obviously done something wrong but we don't know where to look. > The Help button is not helpful at all. > > Any ideas on what we are doing incorrectly? > > Cheers > > Phil Jackson > Using mixed case :) See here for gotchas of using mixed case: http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html 4.1.1. Identifiers and Key Words -- Adrian Klaver adrian.klaver@gmail.com
Hi Adrian The link says that; "Identifier and key word names are case insensitive." But I have renamed the source table in lowercase and this gets me one step further. I'll carry on and see what happens next. Cheers Phil Jackson On 6/30/2010 3:18 PM, Adrian Klaver wrote: > On 07/01/2010 09:46 AM, Phil Jackson wrote: >> We've set up a Sql database for the first time and get an error reported >> back to our application from the ODBC session object when we try to open >> one of the tables. >> >> [42p01][7]ERROR Relation "SqlAnal" does not exist; table not found! >> >> Here is my database outline in the Admin tool >> >> Servers(1) >> Concept Patterns (localhost:5432) >> Databases(1) >> Postgres >> casts >> languages >> schemas >> Public >> - >> - >> Tables(2) >> DocHeader >> SqlAnal >> Replication >> Tablespaces(2) >> pg_default >> pg_global >> Group Roles >> Login Roles(1) >> Concept >> >> We can access the list of tables from the ODBC driver which shows the >> above tables. >> >> We've obviously done something wrong but we don't know where to look. >> The Help button is not helpful at all. >> >> Any ideas on what we are doing incorrectly? >> >> Cheers >> >> Phil Jackson >> > > Using mixed case :) > > See here for gotchas of using mixed case: > > http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html > 4.1.1. Identifiers and Key Words > >
On Thursday 01 July 2010 11:11:29 am Phil Jackson wrote: > Hi Adrian > > The link says that; > > "Identifier and key word names are case insensitive." > > But I have renamed the source table in lowercase and this gets me one > step further. > > I'll carry on and see what happens next. > > Cheers > > Phil Jackson > You need to go to bottom of that section where you would find :) "Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.) " -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On Thursday 01 July 2010 11:11:29 am Phil Jackson wrote:
>> The link says that;
>> "Identifier and key word names are case insensitive."
>>
>> But I have renamed the source table in lowercase and this gets me one
>> step further.
> You need to go to bottom of that section where you would find :)
> ... If you want to write portable applications
> you are advised to always quote a particular name or never quote it.) "
In particular, what probably bit you here is that you created the tables
using a tool that double-quoted the mixed-case names. Once you've done
that, you are condemned to always double-quote those names forevermore.
regards, tom lane
Hi Adrian I had missed that bit. That makes sense now. Cheers Phil Jackson On 6/30/2010 5:04 PM, Adrian Klaver wrote: > On Thursday 01 July 2010 11:11:29 am Phil Jackson wrote: > >> Hi Adrian >> >> The link says that; >> >> "Identifier and key word names are case insensitive." >> >> But I have renamed the source table in lowercase and this gets me one >> step further. >> >> I'll carry on and see what happens next. >> >> Cheers >> >> Phil Jackson >> >> > You need to go to bottom of that section where you would find :) > > "Quoting an identifier also makes it case-sensitive, whereas unquoted names are > always folded to lower case. For example, the identifiers FOO, foo, and "foo" > are considered the same by PostgreSQL, but "Foo" and "FOO" are different from > these three and each other. (The folding of unquoted names to lower case in > PostgreSQL is incompatible with the SQL standard, which says that unquoted > names should be folded to upper case. Thus, foo should be equivalent to "FOO" > not "foo" according to the standard. If you want to write portable applications > you are advised to always quote a particular name or never quote it.) " > >