Hacker News Reader

Comment by lmm | Hacker News Reader

As someone who's used to MySQL, I keep trying to use PostgreSQL (better standards compliance, better behaviour in the face of bad data) - and I keep going back. The psql interface is awkward (too many magic backslash commands to memorize instead of SHOW CREATE TABLE) and always feels slightly laggy somehow.

View on HN

You are the first person I read saying they like the mysql cli. Ctrl-c kills the whole shell. great, but not what I expect. Want to fix a small typo in your big query? In psql you can just edit your last command in your $EDITOR. Useful (but not perfect) autocomplete is also not in mysql.

> In psql you can just edit your last command in your $EDITOR.

Cool! How?

`\e` will edit whatever is in the query buffer (`\p` shows the content of the query buffer)

Just type \e [ENTER]

Autocomplete is in MySQL you might just need to enable it with -A or in the config comment out no-auto-rehash.

> Ctrl-c kills the whole shell. great, but not what I expect.

That is actually exactly what you should expect. SIGINT cancels most things you run in the CLI.

Interrupt a ping because you forgot a count parameter? Ctrl+C

Honestly, the reason I don't use Postgres has nothing to do with the CLI and everything to do with the ecosystem for Multi-Master being inferior to Galera. But yeah, I'm happy with mysql-cli as well and don't really see the Postgres equivalent as a noteworthy improvement.

In Postgres' psql, Ctrl-C cancels the current statement. That's a more useful behavior than killing the entire psql process, IMO.

Ping isn't a CLI, its a single command that expects no further input. A database shell is more like bash than ping. And Ctrl-C does not exit out of bash.

Many language REPLs don't exit on ctrl+c either, at least I know Ruby and Python don't. Node will, but it will make you do it twice.

In MySQL type \e and you can edit your query just the same.

https://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html

I love that earlier up in this same thread people are complaining about the magic \x sequences in postgres and this is how you do query edits in the mysql cli :)

I've got my biases, but whenever something like this comes up, I just make some popcorn and enjoy the show.

It's not really possible to implement "edit the query I'm already partway through" with anything other than a magic sequence. Doesn't mean magic sequences are the right way to do everything.

The complaint was that in psql the backslash sequences are a combination of client operations (\copy, \html) and database metadata operations (\d) [0], which while extremely rich, are a huge list and are not very intuitive to learn. Many differ on case (\df vs \dF). \des and \deu mention the mnemonics for why these are named that way. "External servers" listing "foreign servers" I can understand, but it seems that "external users" are not actually "external".

In MySQL the backslash sequences are commands to the mysql client for things that are implemented in the client and have to do solely with the command line client software. These things are not available using other clients. Vs things like listing columns and databases and functions have their own DDL language constructs entries and are queries in their own right that can be sent by any client software to the server and return iteratable results. MySQL is more like Oracle in this regard.

That distinction isn't obvious in psql and that contributes to a larger hurdle when using the command line client. This makes psql attractive to power users, as there are a lot of shortcuts for things that might require longer DDL statements to be typed or queries against information_schema.

[0] http://www.postgresql.org/docs/9.0/static/app-psql.html

Whenever I do anything more than a oneoff SQL statement (and often even then) I'll use emacs with sql-mode. It supports all the major SQL dialects and cli utilities.

The backslash commands are way better than having to type out SHOW TABLES; or SHOW DATABASES;, or CONNECT DATABASE X. All I have to do is \dt or \c x. It's so much better. Can't remember them? Just do \?.

That's also the problem I have, \dt,\c just don't make sense at all and I never remember them whereas 'SHOW TABLES' does not even need explanations. It's also the same thing with the unix users, I never fully understood how postgres handles user permissions. I would say that Postgres itself is really really good but the tooling around is just not there yet.

I just remembered it as \d as display and \dt as display tables.

I often invent mnemonics that only mean something to me until muscle memory kicks in.

Here's a nice one-page cheat sheet, of which about 75% you will probably never need to use: http://www.postgresonline.com/downloads/special_feature/post...

Thats excellent, thanks :).

Thats like trying to say emacs is easier than eclipse. Sure it may be more efficient once you are used to it, but its not more intuitive.

eclipse and intuitive shouldn't ever be used in the same sentence. emacs ditto, but calling one more intuitive from another is missing the point by about 1 AU.

Eclipse isn't great in that respect, but choosing something from a menu is definitely more intuitive than remembering the cryptic commands in emacs.

"The only intuitive interface is the nipple, after that it's all learned." - Bruce Ediger

Sure but a menu is still more intuitive than C-c.

(I even had to check the fact that C- is the ctrl key when looking up this command. I noticed commands listed as M-w - I have no idea what keys that represents).

M is short for Meta, and usually means "Alt".

\? is killer.

Thanks a ton, I didn't know that yet. I also find MySQL cli very easy to work. But that's going to change now, it seems.

Show create table was so awkward to me after years of \d table. I remember I had to google even desc table and show databases :-)

Anyway, you might want to check http://pgcli.com/ for a free alternative cli with completion. I started using it over the standard psql and I recommend it to everybody.

Admittedly, I haven't used the PG or MySQL cli in years. Coughed up money for a Navicat Premium license about 5 years ago and never looked back.

You should also check out https://www.jetbrains.com/dbe/

I'm fairly sure I signed up for the early access program months ago and still haven't heard back. It's possible that I meant to and didn't though.

http://go.jetbrains.com/HQ0110R0QkKgjD01O6V003M

> It is important to distinguish EAP from traditional pre-release software. Please note that the quality of EAP versions may at times be way below even usual beta standards.

That... scares me, more than a little. I'm okay with it crashing, or losing queries I wrote. I really want failure cases to be restricted to NOT affecting the database, though. I'll probably stay away or try it out on my personal machine on a toy database for now.

Same here. I muddled along with pgAdmin for a while, but now use Valentina Studio. Excellent free app (has an in-app purchase for some advanced features but doesn't feel crippled at all).

Same here. The Valentina Studio free app is very helpful and somewhat smoothens the learning curve

If you haven't already checkout pgadmin-3, I mostly live in the cli but if I need to do something complex or write a large query I often jump into pgadmin as it's display and proper editor are excellent!.

Prefer it to MySQL Workbench even which I liked.

I've recently made the switch to postgres, and then I found pgadmin3, and I'm really feeling some pain.

For instance, the "explain query" visualization is great, and very helpful, but there's no way I can find to zoom out, at all. And that window doesn't support mouse scrolling. And there's no way to export the visualization somewhere else. So when I'm trying to figure out why a very, very large query is running slowly, it's agony to use pgadmin3.

Do you know any good postgres query analysis tools?

It's not graphical, but pasting the explain analyze output into http://explain.depesz.com/ helps me see where postgres is spending most of its time.

That's a bit of a help, thanks!

Count another vote for the pgadmin.

I hate the backslash commands of postgres, about as much as I hate the show commands of mysql (what's not nearly as much as I hate the oracle introspection tools).

Why couldn't SQL standardize database introspection?

it did.

https://en.wikipedia.org/wiki/Information_schema

I didn't know it was a standard, nor that it was widely supported.

Thanks.

This whole thread makes me happy to be a SQL Server user, where the UI tools are standard and robust.

90% of the time, I don't need to remember any special commands for introspecting and since it's on Windows - I can use the keyboard to navigate the entire UI with ease. When I write SQL, I get glorious Intellisense (that's autocomplete to you) for every single object (edit: and command) in the db.

I don't know why you are getting downvoted. I'm not personally crazy about SQL Server, but a lot of big companies are. In this situation some of us find ourselves in from time to time, SQL Management Studio is pretty handy.

I have no idea how they compare but the open-source databases has proprietary database tools too, such as Navicat, which have auto-complete, a good UI (Navicat is hardly beautiful but it works well), etc. There are a few other options too.

I have the same problem. Because of this I use PostgreSQL only if I need it feature-wise and use MySQL as the default. This of course prevents me from getting used to PostgreSQL.

Reply on news.ycombinator.com