Thread: DatabaseMetadata problems

DatabaseMetadata problems

From

Tom Falconer

Date:

07 September 2001, 05:45:35

I am trying to retrieve information about the structure of the database
using the DatabaseMetadata class.  I am using version 7.1.3 and the latest
JDBC Drivers I can find.  I got similar results with the versions supplied
with Mandrake 8.0 (7.0.3 and associated drivers)

The problems are:

1. No Index information from the getIndexInfo method
2. Indexes included as tables in getTables
3. Only one column in the entire database is returned in getColumns
4. The catalog name is not returned in getTables
5. Comment columns contain 'No Comment' instead of null

I have tried many variations in the parameters, but would prefer
catalogName = catalog, schema = null, and tableName = table (where
appropriate).

None of these problems have occurred on Sun's JdbcOdbc Bridge, Oracle's or
MySQL's drivers, so I am fairly confident that my code works.  Is there any
chance of getting these problems fixed?  Where are the sources, as I am
willing to take a look at them to try at least identify the problems in
more detail, even if I don't know Posgres well enough to actually fix them.

Thanks,

Tom

Re: DatabaseMetadata problems

From

Rene Pijlman

Date:

07 September 2001, 07:30:49

On Thu, 6 Sep 2001 22:30:58 +0100, you wrote:
>The problems are:
>1. No Index information from the getIndexInfo method

Hmmm... looking at the source, it apppears that
DatabaseMetaData.getIndexInfo() is implemented. If it doesn't
work for you, we would need more information about the problem
to be able to fix it.

>2. Indexes included as tables in getTables

Again, can you provide us with more detailed information about
the problem? A small test case would be nice, which creates a
few tables and indexes and shows incorrect output.

>3. Only one column in the entire database is returned in getColumns

getColumns() was fixed recently. It returned only columns with a
comment (IIRC). This should be fixed in release 7.2.

>4. The catalog name is not returned in getTables

The PostgreSQL driver intentionally ignores the catalog
parameter of the metadata methods, and it returns null for the
catalog in the metadata it returns.

I don't know if this could be implemented and how. Also, any
interpretation of this concept we come up with has to be
consistent with the backend's understanding of the SQL
standard's concept 'catalog'.

Reading the book "Understanding the SQL standard" it seems that
what the standard calls a schema is a database in PostgreSQL. A
catalog is a set of schemas. Following the standard, a session
is supposed to have access to a catalog, but in PostgreSQL it
has access to a database.

Perhaps this feature has not yet been implemented because of
such fundamental mapping problems.

>5. Comment columns contain 'No Comment' instead of null

That was fixed recently in getTables(), getColumns() and
getProcedures(). The fix wil appear in release 7.2.

>Where are the sources, as I am willing to take a look at them to
>try at least identify the problems in more detail

http://developer.postgresql.org/

I would also like to point you to our JDBC compliance effort:
http://lab.applinet.nl/postgresql-jdbc/. However, the
DatabaseMetaData section is still very much under construction.

I'll be glad to document any deviations of the standard you may
find (please post new issues on this list). Since this is a
volunteer open source project, we cannot guarantee if and when
the problems will be fixed.

Regards,
René Pijlman <rene@lab.applinet.nl>

Re: DatabaseMetadata problems

From

Tom Lane

Date:

07 September 2001, 12:59:28

Rene Pijlman <rene@lab.applinet.nl> writes:
> Reading the book "Understanding the SQL standard" it seems that
> what the standard calls a schema is a database in PostgreSQL. A
> catalog is a set of schemas. Following the standard, a session
> is supposed to have access to a catalog, but in PostgreSQL it
> has access to a database.

> Perhaps this feature has not yet been implemented because of
> such fundamental mapping problems.

I think it'd be a mistake to expend much effort on this issue in JDBC
right now.  We will eventually implement SQL92-compliant schemas in the
backend, and once that happens it'll be possible to do something
reasonable with the catalog metadata routines in JDBC.  If you try
to make a half-usable implementation now, you'll just create backwards-
compatibility issues for the real implementation later.  So my counsel
is: acknowledge that it's broken, but ignore it for now.

When is "eventually"?  Possibly 7.3, but I can't promise anything...

            regards, tom lane

Re: DatabaseMetadata problems

From

Tom Falconer

Date:

07 September 2001, 19:52:37

Rene,

I've attached some sample code, the script I use for creating the database,
and the output when I run it here:


I am sorry, but I didn't notice the Null Pointer Exception being produced
by the getIndexInfo method.  It doesn't seem to matter whether I supply a
table name, schema name or catalog name, I always get the exception.

Thanks for the info on where to get the souurce code and the JDBC
compliance URL.  I'll follow these up when I get time.

Regards,

Tom
Tom_Falconer@lineone.net

-----Original Message-----
From:    Rene Pijlman [SMTP:rene@lab.applinet.nl]
Sent:    07 September 2001 09:13
To:    Tom Falconer
Cc:    'pgsql-jdbc@postgresql.org'
Subject:    Re: [JDBC] DatabaseMetadata problems

On Thu, 6 Sep 2001 22:30:58 +0100, you wrote:
>The problems are:
>1. No Index information from the getIndexInfo method

Hmmm... looking at the source, it apppears that
DatabaseMetaData.getIndexInfo() is implemented. If it doesn't
work for you, we would need more information about the problem
to be able to fix it.

>2. Indexes included as tables in getTables

Again, can you provide us with more detailed information about
the problem? A small test case would be nice, which creates a
few tables and indexes and shows incorrect output.

>3. Only one column in the entire database is returned in getColumns

getColumns() was fixed recently. It returned only columns with a
comment (IIRC). This should be fixed in release 7.2.

>4. The catalog name is not returned in getTables

The PostgreSQL driver intentionally ignores the catalog
parameter of the metadata methods, and it returns null for the
catalog in the metadata it returns.

I don't know if this could be implemented and how. Also, any
interpretation of this concept we come up with has to be
consistent with the backend's understanding of the SQL
standard's concept 'catalog'.

Reading the book "Understanding the SQL standard" it seems that
what the standard calls a schema is a database in PostgreSQL. A
catalog is a set of schemas. Following the standard, a session
is supposed to have access to a catalog, but in PostgreSQL it
has access to a database.

Perhaps this feature has not yet been implemented because of
such fundamental mapping problems.

>5. Comment columns contain 'No Comment' instead of null

That was fixed recently in getTables(), getColumns() and
getProcedures(). The fix wil appear in release 7.2.

>Where are the sources, as I am willing to take a look at them to
>try at least identify the problems in more detail

http://developer.postgresql.org/

I would also like to point you to our JDBC compliance effort:
http://lab.applinet.nl/postgresql-jdbc/. However, the
DatabaseMetaData section is still very much under construction.

I'll be glad to document any deviations of the standard you may
find (please post new issues on this list). Since this is a
volunteer open source project, we cannot guarantee if and when
the problems will be fixed.

Regards,
Rene Pijlman <rene@lab.applinet.nl>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl
Tables:

Dumping ResultSet:
    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: cust_invs
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: cust_name_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: customers_pkey
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: invoice_lines_pkey
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: invoices_pkey
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: parts_pkey
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_aggregate_name_type_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_am_name_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_amop_opid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_amop_strategy_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_attrdef_adrelid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_attribute_relid_attnam_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_attribute_relid_attnum_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_class_oid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_class_relname_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_description_objoid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_group_name_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_group_sysid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_index_indexrelid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_index_indrelid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: char    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_operator_oid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_operator_oprname_l_r_k_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_proc_oid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_proc_proname_narg_type_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_relcheck_rcrelid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_rewrite_oid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_rewrite_rulename_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_statistic_relid_att_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_toast_1215_idx
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_toast_1216_idx
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_toast_1255_idx
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_toast_16600_idx
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_toast_17058_idx
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_toast_17086_idx
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_trigger_tgconstrname_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_trigger_tgconstrrelid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_trigger_tgrelid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_type_oid_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_type_typname_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: INDEX
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: customers
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: invoice_lines
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: invoices
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: parts
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_aggregate
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_am
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_amop
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_amproc
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_attrdef
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_attribute
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_class
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_database
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_description
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_group
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_index
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_inheritproc
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_inherits
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_ipl
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_language
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_largeobject
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_listener
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_opclass
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_operator
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_proc
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_relcheck
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_rewrite
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_shadow
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_statistic
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_trigger
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

    Column 1    Name: TABLE_CAT    Type: varchar    Value: null
    Column 2    Name: TABLE_SCHEM    Type: varchar    Value: null
    Column 3    Name: TABLE_NAME    Type: varchar    Value: pg_type
    Column 4    Name: TABLE_TYPE    Type: varchar    Value: TABLE
    Column 5    Name: REMARKS    Type: varchar    Value: no remarks

---------

End of ResultSet

Indexes:
java.lang.NullPointerException
    at org.postgresql.jdbc2.ResultSet.getBytes(ResultSet.java, Compiled Code)
    at org.postgresql.jdbc2.DatabaseMetaData.getIndexInfo(DatabaseMetaData.java:2716)
    at postgrestest.TestBed.printTest(TestBed.java:50)
    at postgrestest.TestBed.main(TestBed.java:117)
Closing

Attachment

Re: DatabaseMetadata problems

From

Rene Pijlman

Date:

07 September 2001, 22:44:12

On Fri, 07 Sep 2001 10:12:39 +0200, I wrote:
>getColumns() was fixed recently. It returned only columns with a
>comment (IIRC).

No, it returned only columns with a default value, according to
Jeroen van Vianen who fixed it (in 7.2).
See http://fts.postgresql.org/db/mw/msg.html?mid=1032468

Regards,
René Pijlman <rene@lab.applinet.nl>

Re: DatabaseMetadata problems

From

Rene Pijlman

Date:

09 September 2001, 07:17:20

[JDBC driver ignores catalog and schema]

On Fri, 07 Sep 2001 09:58:36 -0400, Tom Lane wrote:
>I think it'd be a mistake to expend much effort on this issue in JDBC
>right now.  We will eventually implement SQL92-compliant schemas in the
>backend, and once that happens it'll be possible to do something
>reasonable with the catalog metadata routines in JDBC.  If you try
>to make a half-usable implementation now, you'll just create backwards-
>compatibility issues for the real implementation later.  So my counsel
>is: acknowledge that it's broken, but ignore it for now.

I agree. I've added this to
http://lab.applinet.nl/postgresql-jdbc/:

"PostgreSQL currently does not support SQL92 compliant catalogs
and schemas. With a number of methods (getColumns, getTables,
getProcedures and such) the JDBC driver ignores the catalog and
schema or schemaPattern parameters. getColumns() returns an
empty string (should be null) in the fields TABLE_CAT and
TABLE_SCHEM. getTables() returns null in TABLE_CAT and
TABLE_SCHEM. getProcedures() returns null in PROCEDURE_CAT and
PROCEDURE_SCHEM."

Regards,
René Pijlman <rene@lab.applinet.nl>