Re: metadata searching

Juriy,

You may not know this, but you can create objects with case sensitive names:

create table "Base" (columna integer);
create table "BASE" (columna integer);
create table Base (columna integer);

The first two creates will create tables whose names are case-sensitive
and thus in order to access these tables you will always need to quote
their names so that the SQL parser knows to retain the case.  The last
create above will create a lowercase named table as postgres folds
unquoted identifiers to lowercase.

Now the interesting thing when it comes to jdbc is that all three of the
above tables can exist at the same time.  So in your example

DatabaseMetaData.getTables(null, "%", name, types) and... (name="Base")...

would return a correct value if the first create above were used.

Because the jdbc doesn't have anyway to say the value for the name in
the method call should be case-sensitive or case-insensitive, it needs
to be assumed to be case sensitive.  And that is why the
DatabaseMetadata class contains other methods that indicate how the
database does case folding so that callers of getTables() and other
methods know how to lowercase or uppercase their table names correctly.

thanks,
--Barry


Juriy Goloveshkin wrote:

> Hello.
>
> I have a problem with jdbc driver.
>
> There is one java program. It can create tables by name if it doesn`t exist.
>
> If tabe name has upper letters like 'Base', then create statement looks like
> 'create Base (...' and postgresql create table in lowercase.
> Then if I want to know is there the table 'Base' I use
>
> DatabaseMetaData.getTables(null, "%", name, types) and... (name="Bases")...
> no tables found.
>
> may be when jdbc driver searches information by name, it is reasonable to
> lowercase it or use ilike in statements?
>
> I think if postgresql is sql-insensetive (select from Base == select from
> base), jdbc have to be insensetive too.
> What do you think?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)