Обсуждение: Table creation
Hi, ALL, I'm new to this list and to PostgreSQL. However, I am a C++ developer with some ODBC development experience. I heard that in PostgreSQL it is possible to create table in 2 ways: CREATE TABLE "Foo" ....; CREATE TABLE Foo... ; So, when using unquoted version all query should check for the case sensitivity. Is it possible to check how the table was created to use proper query syntax? Also SQLTableName/SQLColumnName might fail in this case. Thank you.
On 16/11/2009 10:08 AM, ikorot@earthlink.net wrote: > Hi, ALL, > I'm new to this list and to PostgreSQL. However, I am > a C++ developer with some ODBC development experience. > > I heard that in PostgreSQL it is possible to create table > in 2 ways: > > CREATE TABLE "Foo" ....; > > CREATE TABLE Foo... ; > > So, when using unquoted version all query should check for > the case sensitivity. > > Is it possible to check how the table was created to use proper > query syntax? If the unquoted form was used, the table name will have been downcased by PostgreSQL. It's still case sensitive, just forced to lower case. Essentially, CREATE TABLE Foo ... ; is exactly equivalent to: CREATE TABLE "foo" ... ; in terms of its effects after the CREATE statement has executed. So, if you expect to have any non-lower-case identifiers (table names, index names, constraint names, etc) in your database, ALWAYS quote your identifiers. If they're all lower case, it'll still work. The only problem that'll arise is if your application is told by some external source to access table "Foo" but doesn't know if the external source created it as "Foo" or "foo". In that case, you can either query INFORMATION_SCHEMA with a case-insensitive search to find the table (which is slow and ugly) or just fix your app to be consistent about quoting and case. -- Craig Ringer
Craig, -----Original Message----- >From: Craig Ringer <craig@postnewspapers.com.au> >Sent: Nov 15, 2009 7:02 PM >To: ikorot@earthlink.net >Cc: pgsql-odbc@postgresql.org >Subject: Re: [ODBC] Table creation > >On 16/11/2009 10:08 AM, ikorot@earthlink.net wrote: >> Hi, ALL, >> I'm new to this list and to PostgreSQL. However, I am >> a C++ developer with some ODBC development experience. >> >> I heard that in PostgreSQL it is possible to create table >> in 2 ways: >> >> CREATE TABLE "Foo" ....; >> >> CREATE TABLE Foo... ; >> >> So, when using unquoted version all query should check for >> the case sensitivity. >> >> Is it possible to check how the table was created to use proper >> query syntax? > >If the unquoted form was used, the table name will have been downcased >by PostgreSQL. It's still case sensitive, just forced to lower case. >Essentially, > > CREATE TABLE Foo ... ; > >is exactly equivalent to: > > CREATE TABLE "foo" ... ; > >in terms of its effects after the CREATE statement has executed. So, if >you expect to have any non-lower-case identifiers (table names, index >names, constraint names, etc) in your database, ALWAYS quote your >identifiers. If they're all lower case, it'll still work. So, if I call both formats, i.e.: CREATE TABLE "foo1"...; CREATE TABLE Foo2....; what will SQLTables() return? Thank you. > >The only problem that'll arise is if your application is told by some >external source to access table "Foo" but doesn't know if the external >source created it as "Foo" or "foo". In that case, you can either query >INFORMATION_SCHEMA with a case-insensitive search to find the table >(which is slow and ugly) or just fix your app to be consistent about >quoting and case. > >-- >Craig Ringer > >-- >Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-odbc
On 16/11/2009 1:12 PM, ikorot@earthlink.net wrote: > Craig, > So, if I call both formats, i.e.: > > CREATE TABLE "foo1"...; > > CREATE TABLE Foo2....; > > what will SQLTables() return? I don't know the ODBC interface, so I can't usefully answer your question exactly without doing some digging. The actual tables created by PostgreSQL will be named "foo1" and "foo2" respectively. That's what'll be shown in INFORMATION_SCHEMA and in pg_catalog.pg_class . If SQLTables just enumerates the tables in the database, I expect it should return "foo1" and "foo2". -- Craig Ringer