[development] Postgres equivalent to SHOW TABLES

Daniel Vérité daniel.verite at gmail.com
Sun Sep 6 14:31:11 UTC 2009

	 Nancy Wichmann writes

>   $result = db_query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
> table_name LIKE 'abc\_%'");


> Note that the list had MANY duplicates in it and did not come out in order
> of table name.

In mysql, INFORMATION_SCHEMA.TABLES has all the tables of all databases, not
just the database you're connected to.
This is probably why you get duplicates. To select only the tables of the
current database, which is what SHOW TABLES does, you could add this clause:

...AND table_schema=database()

In postgresql, the schema does not equate the database; it's a namespace
inside a database, so the clause above has to be different. Assuming the db
doesn't use multiple schemas, it could be:

... AND table_schema='public' 

Best regards,
 Daniel Vérité
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org 

More information about the development mailing list