I know it seems dumb, but postgres really needs to add the simple developer experience stuff like:
SHOW CREATE TABLE;
SHOW TABLES;
SHOW DATABASES;
SHOW PROCESSLIST;
CockroachDB added these aliases ages ago.
SHOW CREATE TABLE;
SHOW TABLES;
SHOW DATABASES;
SHOW PROCESSLIST;
CockroachDB added these aliases ages ago.
For anything at home, I would use MySQL just for those things. The psql client feels very primitive by comparison to me - even though it isn't.
During those few weeks I'm actively using the database on the project, I can either get frustrated beyond belief with the CLI for Postgres, or just use what's at hand with MySQL. In fact, these days SQLite is getting more of my attention anyway, and I wrote a small CLI for it a decade or so back (before the sqlite3 client gave us most of the below) to provide:
- Timings for the queries (in fact I called it 'tsql')
- Aligned-column displays, with | separators between columns and index-indicators
- Ability to parse some blobs (Plists on the Mac, for example) and display
- Things like "SHOW DATABASES", "SHOW TABLES", "SHOW TABLES LIKE" etc.
Mainly I wrote it to do some benchmarking, but I eventually preferred it over sqlite3 as the CLI.
Note that all this is personal stuff - When I do commercial stuff, the answer is always "what is best understood by the people maintaining it afterwards"...
\dt
\l
SELECT * FROM pg_stat_activity;
Not really; PostgreSQL doesn't store the original query, so you'll need to re-create it from pg_class, pg_attribute, and all of that (which is really what \d and such in psql do). The easiest way is probably pg_dump, but it's best to just get used to \-commands because it's really just the same thing.