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.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Table creation

Craig,

-----Original Message-----
>From: Craig Ringer
>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


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Table creation

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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc