5 ms·

SHOW DATABASES; and SHOW TABLES;, please. DESC would be nice too. And let it work everywhere, not just in CLI (like \d & co. do).

leo8 4y ago
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)
- deathanatos 4y ago
  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?)
  - leo8 4y ago
    Ok, that makes no sense, now I see. I was talking about SHOW TABLES actually. In pgAdmin I would have to expand every schema to see every table. Was a bit tired when writing that comment.
    - deathanatos 4y ago
      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.)
sroussey 4y ago
Also, MySQL's `show create table ...` would be great.
- drodgers 4y ago
  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-utils/d615e5b3001029c49ade560ab7d3695b354865cf/src/AdminViews/v_generate_tbl_ddl.sql https://raw.githubusercontent.com/awslabs/amazon-redshift-ut...) and I'd guess that you can do the same for Postgres.
arp242 4y ago
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.
- bornfreddy 4y ago
  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.
  - arp242 4y ago
    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.
zX41ZdbW 4y ago
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-the-limit-statement-in-sql https://stackoverflow.com/questions/1528604/how-universal-is... That's why ClickHouse has support for most of the extensions from the MySQL dialect.
hholzgra 4y ago
SHOW CREATE TABLE and friends even more so

leo8 4y ago

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)

deathanatos 4y ago
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?)
- leo8 4y ago
  Ok, that makes no sense, now I see. I was talking about SHOW TABLES actually. In pgAdmin I would have to expand every schema to see every table. Was a bit tired when writing that comment.
  - deathanatos 4y ago
    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.)

sroussey 4y ago

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

drodgers 4y ago
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-utils/d615e5b3001029c49ade560ab7d3695b354865cf/src/AdminViews/v_generate_tbl_ddl.sql https://raw.githubusercontent.com/awslabs/amazon-redshift-ut...) and I'd guess that you can do the same for Postgres.

arp242 4y ago

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.

bornfreddy 4y ago
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.
- arp242 4y ago
  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.

zX41ZdbW 4y ago

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-the-limit-statement-in-sql https://stackoverflow.com/questions/1528604/how-universal-is... That's why ClickHouse has support for most of the extensions from the MySQL dialect.

hholzgra 4y ago

SHOW CREATE TABLE and friends even more so