By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,175 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,175 IT Pros & Developers. It's quick & easy.

how to read tables from database

P: 32
I have to get all the table names that exist in a particular database in pgsql and print it in a html page.
How can i do this?
Mar 2 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 534
One way to get all user tables in Postgresql is to run this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT c.relname as "Name"
  2.   FROM pg_catalog.pg_class c
  3.   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  4. WHERE c.relkind IN ('r','')
  5.   AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  6.   AND pg_catalog.pg_table_is_visible(c.oid)
  7. ORDER BY 1
  8.  
If you have one of the latest versions of the server you can make use of
Information Schema

This would be a preferred approach.

All this implies that you need to submit your query using some client API, such as PHP, but if you open the psql session the command is simply
\d

Hope it helps.
Mar 2 '07 #2

Expert 100+
P: 534
Forgot to mention: as far as making connection and processing query results using PHP you need to look here
Mar 2 '07 #3

P: 32
One way to get all user tables in Postgresql is to run this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT c.relname as "Name"
  2.   FROM pg_catalog.pg_class c
  3.   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  4. WHERE c.relkind IN ('r','')
  5.   AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  6.   AND pg_catalog.pg_table_is_visible(c.oid)
  7. ORDER BY 1
  8.  
If you have one of the latest versions of the server you can make use of
Information Schema

This would be a preferred approach.

All this implies that you need to submit your query using some client API, such as PHP, but if you open the psql session the command is simply
\d

Hope it helps.
It is not working. It show this error
Warning: PostgreSQL query failed: ERROR: parser: parse error at or near "'" in /web/train06/myphp/ex.php on line 14. What is the problem?
How to display the table names?
Mar 6 '07 #4

Expert 100+
P: 534
This sql works perfectly well for me.
I suspect that you may need to escape the single quotes in your PHP code.
Since you did not post any code snippets it is hard to pinpoint the problem.
Mar 6 '07 #5

ronverdonk
Expert 2.5K+
P: 4,258
It is not working. It show this error
Warning: PostgreSQL query failed: ERROR: parser: parse error at or near "'" in /web/train06/myphp/ex.php on line 14. What is the problem?
How to display the table names?
Since you ask the question in the PHP forum, we can show you how to code Pgsql commands in php.

What you should know, as the pgsql programmer/user, is how the pgsql command to show the tables is constructed. Like in MySQL it is SHOW TABLES". There must be something alike in pgsql.

Ronald :cool:
Mar 6 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.