Thread: metadata searching

metadata searching

From
"Juriy Goloveshkin"
Date:
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?



Re: metadata searching

From
Oliver Jowett
Date:
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?

What happens when you have two tables that differ only in case? Seems
like the current behaviour is correct to me.

-O

Re: metadata searching

From
Mofeed Shahin
Date:
On Sun, 1 Feb 2004 06:17 pm, you 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.

Is that supposed to be name="Base" and not "Bases" ?

Mof.


Re: metadata searching

From
Barry Lind
Date:
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)


Re: metadata searching

From
Oliver Jowett
Date:
Juriy Goloveshkin wrote:

>>What happens when you have two tables that differ only in case? Seems
>>like the current behaviour is correct to me.
>
>
> Ok. Are You want to say that the different behavior in sql engine and jdbc
> code is normal?
> why I can build sql statement like 'select from Base' and 'select from base'
> while I have only one table 'base',
> but  getTables(..., "Base", ...) and getTables(..., "base", ...) gives me a
> diffenent result.
> Where is the logic?
> What about psql?
> \d Base
> \d base
> gives me info about the table 'base'. but not jdbc getTables(). pretty nice.
>
> I think the logic of things like getTables have to be the same as the sql
> engine.

You're only seeing inconsistencies because you're using unquoted
identifiers on the SQL side. Java strings are case-sensitive; the
behaviour of getTables is consistent with what you get when using
case-sensitive (i.e. quoted) SQL identifiers. Witness:

$ psql testdb
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
[...]
testdb=> create table "test" (k int4);
CREATE TABLE
testdb=> create table "Test" (q int4);
CREATE TABLE
testdb=> \d "test"
      Table "public.test"
  Column |  Type   | Modifiers
--------+---------+-----------
  k      | integer |

testdb=> \d "Test"
      Table "public.Test"
  Column |  Type   | Modifiers
--------+---------+-----------
  q      | integer |

-O

Re: metadata searching

From
Oliver Jowett
Date:
Oliver Jowett wrote:

> $ psql testdb
> Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
> [...]
> testdb=> create table "test" (k int4);
> CREATE TABLE
> testdb=> create table "Test" (q int4);
> CREATE TABLE
> testdb=> \d "test"
>      Table "public.test"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  k      | integer |
>
> testdb=> \d "Test"
>      Table "public.Test"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  q      | integer |

Eh, and of course the case actually relevant to your original example..

testdb=> \d test
      Table "public.test"
  Column |  Type   | Modifiers
--------+---------+-----------
  k      | integer |

testdb=> drop table test;
DROP TABLE
testdb=> \d test
Did not find any relation named "test".
testdb=> \d "Test"
      Table "public.Test"
  Column |  Type   | Modifiers
--------+---------+-----------
  q      | integer |

-O

Re: metadata searching

From
"Juriy Goloveshkin"
Date:
> > 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?
>
> What happens when you have two tables that differ only in case? Seems
> like the current behaviour is correct to me.

Ok. Are You want to say that the different behavior in sql engine and jdbc
code is normal?
why I can build sql statement like 'select from Base' and 'select from base'
while I have only one table 'base',
but  getTables(..., "Base", ...) and getTables(..., "base", ...) gives me a
diffenent result.
Where is the logic?
What about psql?
\d Base
\d base
gives me info about the table 'base'. but not jdbc getTables(). pretty nice.

I think the logic of things like getTables have to be the same as the sql
engine.



Re: metadata searching

From
"Juriy Goloveshkin"
Date:
> 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);
I know this. it's good if it's an sql-standard. What does it change?
>
> 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.
sql-engine can work with whis. Why jdbc code doesn't?
Did you play with your example in psql with \d <name> command? Something
different with getTable in jdbc? :) Why?