Thread: metadata searching
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?
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
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.
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)
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
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
> > 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.
> 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?