Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    23

    Unanswered: Tables in a Postgres database (not schema)

    Hello All -

    I am looking to see if there is a way to query all the tables in a database (not schema) in Postgres?

    I have used SELECT * FROM pg_catalog.pg_tables where schemaname = 'schemaname'

    But this query works only when i log in with username and password and it can execute for that user.

    I want to log in as user postgress (not safe I know) and look up the tables in a database regardless of schema.

    Kind of like 'SHOW tables FROM databasename' in MySQL executed as root.

    I need to execute this as a script from Python. I can create database, user etc. as user postgres from Python. Need a query to see the tables in the database before I drop the database.

    ...
    In saying that - I have another question. Is it a bad idea to create tables directly in a database in Postgres, without creating a schema? ie create tables in 'public'?

    Meaning I should create a database, create schemas in that database, grant a user certain schema(s) and then user creates tables in schemas?

    Just wanted to confirm if bypassing the schema is just not done or it does not matter?

    I am currently creating tables directly in the database by a user - from Python - and trying out different loads and it works fine. Including dropping tables and such. Not using specific tablespaces currently.

    I am also working in MySQL so kind of wanted to keep the same pattern as MySQL does not have schemas.

    I assume working in the schema has more advantages with security, grants, roles, tablespaces and such than directly in database (like Oracle)?

    Just wanted to confirm what may be pitfalls in working directly in database w/o the schema.

    ...
    Thank you for your help.

    Thank you for your time and patience in answering the somewhat winded and basic questions.

    Mono

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by mdr View Post
    I have used SELECT * FROM pg_catalog.pg_tables where schemaname = 'schemaname'
    If you want all tables, then leave out the condition on the schema.


    But this query works only when i log in with username and password and it can execute for that user.
    Of course, because you can only see those tables that you have access to. In order to see all tables, you have to use a user that has access to all tables. That is typically the superuser.


    I want to log in as user postgress (not safe I know) and look up the tables in a database regardless of schema.
    Again, just leave out the condition on the schema. Logging in as postgres is the usual way if you need to do DBA work. That's what the account is for.


    Kind of like 'SHOW tables FROM databasename' in MySQL executed as root.
    It's probably better to use the information schema instead, but again: just leave out the condition on the schema:
    Code:
    select table_name 
    from information_schema.tables

    In saying that - I have another question. Is it a bad idea to create tables directly in a database in Postgres, without creating a schema?
    You can't. A table is always stored in a schema.


    ie create tables in 'public'?
    There is nothing wrong with storing tables in the public schema, that's what it's for. And it's probably what the vast majority of people are doing. There is nothing special about the public schema, except that it's created by default and the default search_path includes it.


    Just wanted to confirm if bypassing the schema is just not done or it does not matter?
    You can't "bypass" the use of schemas.


    Not using specific tablespaces currently.
    Tablespaces are something completely different than schemas. Tablespace is a phyiscal organisation, schemas are logical namespace.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jun 2013
    Posts
    23
    Thanks Shammat for your answers. I had tried what you suggest and the queries did not return anything.

    To clarify, here is my situation. I have 2 databases (not schema) import_dbms_db1 and import_dbms_db2 and 2 users import_dbms_u1 and import_dbms_u2 who have been granted ALL PRIVILEGES on each database (not schema) directly.

    The database, users and grants are created from a Python script logged in as user Posstgres - works fine.

    i create tables in each database logged in as each user from my Python script and load data in them - no problem.

    Now I want to see a list of the tables in each database. So I log in from console and have run various queries - none return anything:

    postgres=# SELECT * FROM pg_catalog.pg_tables where tableowner = 'import_dbms_u1';
    schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
    ------------+-----------+------------+------------+------------+----------+-------------
    (0 rows)

    and also -
    SELECT * FROM information_schema.tables where table_schema = 'public';

    table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action
    ---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------

    All I want is to see the tables in a database, in this case under schema 'public'.

    I am ok to include username as well as I tried above.

    However, it seems they may not be possible as those queries have 'schemaname' and 'table_schema' fields, but nothing to indicate a database name?

    Again, as per example of MySQL cited, I am looking for something like:
    show tables from import_dbms_db1;

    Btw I understand the difference regards tablespaces and schemas. The only reason I mentioned tablespace is because there is a column for tablespace in those queries mentioned. However, there is no database column.

    I am thinking there has to be a way to just get the tables in a database and then schema if needed as user postgres?

    Thank you again for your help and look forward to your reply.

    Mono

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    You can't "access" another database without changing the connection. A query (or a connection) can never leave the boundary of database (ignoring things like DBLink or FDW for now).

    Databases in MySQL are more like schemas in Postgres, not like databases (Note that in MySQL the words schema and database are actually a synonym).

    So if you are porting from MySQL to Postgres then create a single database in Postgres and one Postgres schema for each MySQL database.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jun 2013
    Posts
    23
    Thanks Shammat for your reply.

    So just for final clarification - you cannot access tables and such in other databases even as user postgres who created the database in the first place?

    So to query the tables, views, indexes have to log into the database.

    Can this be done as the user postgres - or the user who has had grant all to have had created the tables initially, the import_dbms_u1 and u2 users as mentioned before?

    I can check as well by trial.

    Thank you again.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by mdr View Post
    So just for final clarification - you cannot access tables and such in other databases even as user postgres who created the database in the first place?
    Correct. You can only access tables inside the database you are connected to
    (There are solutions for cross-database queries such as DBLink or a postgres_fdw, but those create a new connection as well).


    So to query the tables, views, indexes have to log into the database.
    Yes.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Jun 2013
    Posts
    23
    Thanks Shammat.

    It is great to get this clairification.

    I have been looking for a clear answer to this fact for a while now.

    I believe it is a good thing this is the case for security, but much inconvenience happens when looking across different databases.

    Anyway, no problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •