current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

actually i have googled a bit and i need corresponding SELECT command to following PostgreSQL shell command : 

\dt schemaname.*

i managed to get all databases with following code : 

            Statement statement = (Statement) connection.createStatement();
            ResultSet rs = statement
                    .executeQuery("SELECT datname FROM pg_database");
            while (rs.next()) {
                System.out.println("DB Name : " + rs.getString(1));
           //i need another while here to list tables 
           //inside the selected database
}

i tried following statement, but no luck :

statement.executeQuery("SELECT table_schema,table_name FROM "
                                + rs.getString(1)
                                + " ORDER BY table_schema,table_name");

this is the error i am getting :

org.postgresql.util.PSQLException: ERROR: relation "template1" does not exist
  Position: 37
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    at com.isiran.rayten.rg.db.bare.wrapper.PGWrap.main(PGWrap.java:64)
share|improve this question
    
the connection string is wrong, you are pointing into template1 database. – user2511414 Oct 28 '13 at 10:08
    
You are apparently using the database name as the table name in your second executeQuery() – a_horse_with_no_name Oct 28 '13 at 10:09
up vote 2 down vote accepted

To list all tables from database you have to read table pg_catalog.pg_tables But unfortunately you have to be logged-in in database. So in place where you wrote comments

//i need another while here to list tables 
//inside the selected database

Before loop for tables you need to log-in in this database.

share|improve this answer
    
and how can i do that? i know i can do it by typing connect schema in shell but not sure how to do that it java – MoienGK Oct 28 '13 at 10:46
    
In the same way as object @connection@ was initialized in your code, but with different database name. But you need user name and password for each database or connect to databases with superuser user (e.g. postgres). – Nicolai Oct 28 '13 at 10:50
    
hmm. i have already connected to that database, with jdbc connection string :-? – MoienGK Oct 28 '13 at 10:59
1  
Yes, but you try to list tables from other databases. You can't do this from current database. – Nicolai Oct 28 '13 at 11:00
up vote 4 down vote

If you use psql -E, it'll echo the actual queries that get run when you type commands such as \dt:

denis=# \dt public.*
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
      AND n.nspname !~ '^pg_toast'
  AND n.nspname ~ '^(public)$'
ORDER BY 1,2;
**************************

These queries can then be simplified or amended per your specific use-case.

share|improve this answer
    
+1 for helpful comment, but as Nicolai mentioned i have to connect to schema first, i am not sure how to do that within java since it is not a query – MoienGK Oct 28 '13 at 10:49
up vote 1 down vote

Use the DatabaseMetaData object to query information, eg getTables(...):

DatabaseMetaData dbmd = connection.getMetaData();
try (ResultSet tables = dbmd.getTables(null, null, "%", new String[] { "TABLE" })) {
    while (tables.next()) {
        System.out.println(tables.getString("TABLE_NAME"));
    }
}

This will return all tables in the database, you may need to specify values for catalog and/or schemaPattern to get a more specific result.

share|improve this answer
    
where does schema name go in above command? – MoienGK Oct 28 '13 at 10:51
    
If you follow the link to the javadoc, you can see that schemaPattern is the second parameter. – Mark Rotteveel Oct 28 '13 at 11:21
    
it is working but the problem is it cant load db names so i cant pass the name as argument – MoienGK Oct 28 '13 at 14:04
    
As far as I know getSchemas() is implemented for PostgreSQL – Mark Rotteveel Oct 29 '13 at 13:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.