This a thousand times. I have to ALWAYS google that one big query for showing me all the databases. (I almost always connect with pgAdmin)

I'm confused; in what context are you always googling it? In pgAdmin, its displayed in the UI, no?

(I sort of agree with the parent, in that, if you need this in a raw SQL interface, it's a pain. But it shouldn't be a common pain point?)

Ah, another UI that hides information by default. Those are annoying.

(I'm a CLI user, so it's a \dt away. But I Googled screenshots of pgAdmin prior to commenting to see if it was in the UI, and it/was.)

Also, MySQL's `show create table ...` would be great.

I've never looked into this for Postgres itself, but AWS Redshift (a postgres derivative) has enough info in the system tables to reconstruct a create table statement using a view (see: https://raw.githubusercontent.com/awslabs/amazon-redshift-ut...) and I'd guess that you can do the same for Postgres.

You can use "select * from pg_stat_user_tables" etc.

Admittedly the internal tables and views have a bit of a learning curve and are a little bit obscure (and inconsistent) at times, but it's much nicer and much more flexible.

The CLI stuff like \d are just "aliases" for queries; \set echo_hidden shows them.

Of course you can, but that is beside the point. Convenience matters - besides, these approaches can coexist, adding those simple queries (as aliases) should be trivial.

SHOW has well-defined consistent semantics. Adding "special shortcuts" doesn't seem like much of an improvement.

From the CLI \d etc. seems just as easy (easier actually), and you can add your own shortcuts if you want.

From outside the CLI, this sort of introspection is rare enough I don't really see the point in making special shortcuts for it.

Although it has its gotchas, MySQL dialect is still somewhat better from the usability standpoint. "SHOW TABLES" is nice; it is natural and self-explanatory. "\d" is obscure.

It also reminds me how many DBMSs did not support the LIMIT clause because it is non-standard. But it is good from a usability perspective.

https://stackoverflow.com/questions/1528604/how-universal-is...

That's why ClickHouse has support for most of the extensions from the MySQL dialect.

SHOW CREATE TABLE and friends even more so