Re: 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.

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