Re: Cannot open table in new database
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)
ConceptWe 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
Import Notes
Reply to msg id not found: 4C2CC661.5050108@clear.net.nzReference msg id not found: 4C2CC661.5050108@clear.net.nz
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
Import Notes
Reply to msg id not found: 4C2CDA51.5010608@clear.net.nzReference msg id not found: 4C2CC661.5050108@clear.net.nz
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
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
Show quoted text
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)
ConceptWe 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
Hi Adrian
I had missed that bit. That makes sense now.
Cheers
Phil Jackson
Show quoted text
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.) "