PostgreSQL Problems

[tomk@hepburn]

I'm new to Alpha5 but an experienced database administrator. I've just started to build my first "learning" project but have already come across a major problem which could turn out to be a show-stopper. I have a PostgreSQL 9.0 database that has all field names in tables in uppercase (this is a non-negotiable standard). That means these tables have to be referenced explicitly with quotation marks (e.g. select dbledger."ACCOUNT_CODE" from dbledger ... "). I can build the connection string and connect to the database just fine, however my first attempt at looking at table data using the database explorer fails with the error message shown below.

Database API specific error. Your database has returned the following error code and description to Alpha5. Consult your database documentation for further information.

7 - Error. Column "account_code" does not exist. Error while executing the query.

The error is correct, the column "account_code" does not exist, however the column "ACCOUNT_CODE" does exist. It seems that when building a query, Alpha5 ignores case in field names, even though field names are shown in upper case in the database explorer. PostgreSQL field names are case sensitive. Is there anywhere in Alpha5 where I can set an "Options" parameter to tell Alpha5 NOT to ignore case in database field names?

[tomk@hepburn]

Re: PostgreSQL Problems

Here's an extract from the PostgreSQL documentation:

"Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

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.) "

So it seems Alpha5 does not handle quoted names when generating a SQL statement for a PostgreSQL connection. I'll go through the tutorials and see if I can generate pass-through SQL in native PostgreSQL syntax to overcome the problem, although this will be more cumbersome than it may be worth.

[bea2701]

Re: PostgreSQL Problems

Here's an extract from the PostgreSQL documentation:

"Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

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.) "

So it seems Alpha5 does not handle quoted names when generating a SQL statement for a PostgreSQL connection. I'll go through the tutorials and see if I can generate pass-through SQL in native PostgreSQL syntax to overcome the problem, although this will be more cumbersome than it may be worth.

I don't see this as a bug at present but i understand your situation, send a request to [email protected] perhaps he can help to solve and fix your issue.