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
If you're looking for an equivalent to MySQL et al.'s "SHOW TABLES" see psql's \d command...
Thomas Christensen
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?