At 09:41 23/10/00, you wrote:

...

Hmm, I'm not very clear about differences between handling of (table, column) names in sql-databases, but postgres IS case sensitive (in some sense).

Well I'm confused !!!

I have two databases , one MySQL (3.23.25) one PostgreSQL (7.01), both with the same schema. Both have a table 'artist'. The following SQL Method (Zope 2.2.1) works with both

select a.artist_id,a.known_name,a.initials

...

from artist a where <dtml-sqltest surname type=string>

Change this to

select a.artist_id,a.known_name,a.initials

...

from ARTIST a where <dtml-sqltest surname type=string>

it still works with PostgreSQL but with MySQL I get

"Table 'tunedb.ARTIST' doesn't exist"

Change this to

select a.artist_id,a.known_name,a.initials

...

from ARTIST a where <dtml-sqltest SURNAME type=string>

Then it still works with PostgreSQL, provided you also change the argument to the SQL Method from surname to SURNAME.

When you click on the Test tab of an SQL Method it shows the SQL generated and you can see, in the above example, that the uppercase words are passed through unchanged to the database

viz.

select a.artist_id,a.known_name,a.initials from ARTIST a where SURNAME = 'Molloy'

In what circumstances is PostgreSQL case-sensitive ?

First, sorry for my (nearly) full-quote, but I think it makes this discussion more readable. To your question, from my testing (I did this with column names only, but I suspect the same behavior for table-names), postgres is case sensitive, but - as I wrote - in another sense. If you don't quote the column-names, they are converted to or interpreted as lowercased names. _But_ if the real name of the table/column is _uppercase_, it will not be matched by this lowercased name - in this sense postgres is case-sensitive. Postgres needs quoted names in sql-queries to respect capitalization (sp?) - and I couldn't <dtml-sqltest ...> get to generate them. This was a major problem for me, because I had to work with a bunch of tables which were exported from foxpro and dbase, and their names were uppercased in the process. cheers, oliver