Thread: NetBeans (3.5 Dev), Postgresql 7.4dev jdbc
Hello,
I try Database explorer with Netbeans and Postgresql (it is same with forte and postgresql 7.3.2 jdbc)
I can't get tables, views , procedures.
Problem is schema name - btw. when Database explorer call DatabaseMetadata.getTables use only upper case
(no matter if schema lower case) and don't find anything.
I think that problem is Netbeans, because lower case schema name is allowed.
- schema (and user,tables, procedures etc) don't be set in upper case
BTW. I have user (schema) snpe :
Netbeans Database Explorer call next query for tables (query is ugly, but PostgreSQL haven't friendly catalog) :
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,
CASE n.nspname LIKE 'pg\\_%'
WHEN true THEN
CASE n.nspname
WHEN 'pg_catalog' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL END
WHEN 'pg_toast' THEN CASE c.relkind
WHEN 'r' THEN 'SYSTEM TOAST TABLE'
WHEN 'i' THEN 'SYSTEM TOAST INDEX'
ELSE NULL
END
ELSE CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE'
WHEN 'i' THEN 'TEMPORARY INDEX'
ELSE NULL
END
END
WHEN false THEN
CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN
'VIEW' ELSE NULL END ELSE NULL END
AS TABLE_TYPE,
d.description AS REMARKS
FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
WHERE c.relnamespace = n.oid
** AND n.nspname LIKE 'SNPE'
AND c.relname LIKE '%'
AND (false OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) )
ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
Row with ** make problem
I try < AND n.nspname like 'snpe' > and it work.
Who will resolve : Netbeans or Postgresql JDBC people ?
regards
Haris Peco
It is my understanding that SQL92 calls for all names to be CASE-INSENSITIVE and that PostgreSQL is broken in this respect. Perhaps we can get some code introduced to munge cases in the parser (as a configuration option to avoid the performance penalty for those who care to be case sensitive). Charlie On Thursday, April 3, 2003, at 08:23 PM, snpe wrote: > Hello, > I try Database explorer with Netbeans and Postgresql (it is same with > forte and postgresql 7.3.2 jdbc) > I can't get tables, views , procedures. > Problem is schema name - btw. when Database explorer call > DatabaseMetadata.getTables use only upper case > (no matter if schema lower case) and don't find anything. > I think that problem is Netbeans, because lower case schema name is > allowed. > - schema (and user,tables, procedures etc) don't be set in upper case > > BTW. I have user (schema) snpe : > Netbeans Database Explorer call next query for tables (query is ugly, > but PostgreSQL haven't friendly catalog) : > > SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS > TABLE_NAME, > CASE n.nspname LIKE 'pg\\_%' > WHEN true THEN > CASE n.nspname > WHEN 'pg_catalog' THEN > CASE c.relkind > WHEN 'r' THEN 'SYSTEM TABLE' > WHEN 'v' THEN 'SYSTEM VIEW' > WHEN 'i' THEN 'SYSTEM INDEX' > ELSE NULL END > WHEN 'pg_toast' THEN CASE c.relkind > WHEN 'r' THEN 'SYSTEM TOAST TABLE' > WHEN 'i' THEN 'SYSTEM TOAST INDEX' > ELSE NULL > END > ELSE CASE c.relkind > WHEN 'r' THEN 'TEMPORARY TABLE' > WHEN 'i' THEN 'TEMPORARY INDEX' > ELSE NULL > END > END > WHEN false THEN > CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' > THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' ELSE NULL END ELSE NULL END > AS TABLE_TYPE, > d.description AS REMARKS > FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c > LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND > d.objsubid = 0) > LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND > dc.relname='pg_class') > LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND > dn.nspname='pg_catalog') > WHERE c.relnamespace = n.oid > ** AND n.nspname LIKE 'SNPE' > AND c.relname LIKE '%' > AND (false OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) ) > ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME > > Row with ** make problem > I try < AND n.nspname like 'snpe' > and it work. > > Who will resolve : Netbeans or Postgresql JDBC people ? > > regards > Haris Peco > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
SQL92 is quite old, what does sql99 say about case sensitivity? also I'm not sure what exactly the issue is here? as long as the names are not surrounded by ' then postgres is case insensitive. Dave On Fri, 2003-04-04 at 08:53, Charlie Woloszynski wrote: > It is my understanding that SQL92 calls for all names to be > CASE-INSENSITIVE and that PostgreSQL is broken in this respect. > Perhaps we can get some code introduced to munge cases in the parser > (as a configuration option to avoid the performance penalty for those > who care to be case sensitive). > > > > Charlie > > > On Thursday, April 3, 2003, at 08:23 PM, snpe wrote: > > > Hello, > > I try Database explorer with Netbeans and Postgresql (it is same with > > forte and postgresql 7.3.2 jdbc) > > I can't get tables, views , procedures. > > Problem is schema name - btw. when Database explorer call > > DatabaseMetadata.getTables use only upper case > > (no matter if schema lower case) and don't find anything. > > I think that problem is Netbeans, because lower case schema name is > > allowed. > > - schema (and user,tables, procedures etc) don't be set in upper case > > > > BTW. I have user (schema) snpe : > > Netbeans Database Explorer call next query for tables (query is ugly, > > but PostgreSQL haven't friendly catalog) : > > > > SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS > > TABLE_NAME, > > CASE n.nspname LIKE 'pg\\_%' > > WHEN true THEN > > CASE n.nspname > > WHEN 'pg_catalog' THEN > > CASE c.relkind > > WHEN 'r' THEN 'SYSTEM TABLE' > > WHEN 'v' THEN 'SYSTEM VIEW' > > WHEN 'i' THEN 'SYSTEM INDEX' > > ELSE NULL END > > WHEN 'pg_toast' THEN CASE c.relkind > > WHEN 'r' THEN 'SYSTEM TOAST TABLE' > > WHEN 'i' THEN 'SYSTEM TOAST INDEX' > > ELSE NULL > > END > > ELSE CASE c.relkind > > WHEN 'r' THEN 'TEMPORARY TABLE' > > WHEN 'i' THEN 'TEMPORARY INDEX' > > ELSE NULL > > END > > END > > WHEN false THEN > > CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' > > THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' ELSE NULL END ELSE NULL END > > AS TABLE_TYPE, > > d.description AS REMARKS > > FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c > > LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND > > d.objsubid = 0) > > LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND > > dc.relname='pg_class') > > LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND > > dn.nspname='pg_catalog') > > WHERE c.relnamespace = n.oid > > ** AND n.nspname LIKE 'SNPE' > > AND c.relname LIKE '%' > > AND (false OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) ) > > ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME > > > > Row with ** make problem > > I try < AND n.nspname like 'snpe' > and it work. > > > > Who will resolve : Netbeans or Postgresql JDBC people ? > > > > regards > > Haris Peco > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Dave Cramer <Dave@micro-automation.net>
Charlie Woloszynski <chw@clearmetrix.com> writes:
> It is my understanding that SQL92 calls for all names to be
> CASE-INSENSITIVE and that PostgreSQL is broken in this respect.
The spec calls for unquoted names to be case insensitive. They are,
in Postgres. The spec also calls for quoted names to be case sensitive.
They are, in Postgres. The place where we diverge from the spec is in
what happens when you write what you intend to be the same name both
with and without quotes at different times. I'd suggest that that is
bad style and you'd best not do it. For one thing, the implied case
conversion is locale-specific (yes, that's per spec too) --- so SQL
that works in one locale may fail in another. The only way to defend
agains the locale risk is to consistently write any given name either
always with quotes, or always without. If you do that then Postgres'
deviation from the spec isn't going to affect you.
For the record, unquoted foo (regardless of case) equates to "FOO" per
spec, but "foo" in Postgres.
regards, tom lane
There are methods in DatabaseMetadata like storesLowerCaseIdentifiers(), storesMixedCaseInetifiers() (postgresql return corect value for this) and netbeans module db need use this methods regards Haris Peco On Friday 04 April 2003 03:09 pm, Tom Lane wrote: > Charlie Woloszynski <chw@clearmetrix.com> writes: > > It is my understanding that SQL92 calls for all names to be > > CASE-INSENSITIVE and that PostgreSQL is broken in this respect. > > The spec calls for unquoted names to be case insensitive. They are, > in Postgres. The spec also calls for quoted names to be case sensitive. > They are, in Postgres. The place where we diverge from the spec is in > what happens when you write what you intend to be the same name both > with and without quotes at different times. I'd suggest that that is > bad style and you'd best not do it. For one thing, the implied case > conversion is locale-specific (yes, that's per spec too) --- so SQL > that works in one locale may fail in another. The only way to defend > agains the locale risk is to consistently write any given name either > always with quotes, or always without. If you do that then Postgres' > deviation from the spec isn't going to affect you. > > For the record, unquoted foo (regardless of case) equates to "FOO" per > spec, but "foo" in Postgres. > > regards, tom lane