Monday, August 09, 2010

PostgreSQL: show tables;

I was using DB Designer Fork with PostgreSQL. Because it does not have direct support to PostgreSQL I downloaded ODBC driver of it and set up an ODBC data source. After using this ODBC data source DB Designer Fork could connect to the database and reverse-engineered the database. Then I got strange error after I tried to sync to the database after some change made to the database and found the error caused by "show tables;"

I had no clue of what's wrong because I remembered "show tables" is a command. After a few hours digging, I found this:
http://www.postgresql.org/docs/8.0/interactive/sql-show.html At the very bottom of this page:
Joe 12 Mar 2005 15:33:00
If you're looking for an equivalent to MySQL et al.'s "SHOW TABLES" see psql's \d command...
Thomas Christensen 03 Jun 2005 9:08:06
For functionality similar to mysql SHOW TABLES use:
select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE'

It turns out, PostgreSQL does not support this command.

I am still wondering why there is no error to fetch tables in the first place when reverse-engineer it? Does it use another standard SQL command?