Psql command line tutorial and cheat sheet | Hacker News Reader

Psql command line tutorial and cheat sheet

tomcam.github.io

328 pointstomcam44 comments

To complement this, I recommend installing `pgcli`[https://www.pgcli.com/]. It's a near-drop-in-replacement for psql, and gives autocompletion, syntax highlighting, and semicolon forgiveness.

Even as someone already comfortable with psql, pgcli is extremely handy.

I second this motion.

I went back and forth between psql and pgcli for several years, until I realized that pgcli tab-completes JOIN conditions.

Postgres supports JOIN USING. If your keys are named the same in both tables, say `user_id` then you can simply say

  SELECT *
  FROM parent
  JOIN child USING (user_id)

I personally think naming your primary keys with their full name instead of calling all of them `id` or `key` is better practice also.

Thanks! Just switched to this for a project I'm working on.

A drop-down autocomplete in a console? First time I've seen such a thing. Thanks for pointing out this tool.

That's part of prompt toolkit, a python library for building cli applications. Its creator actually reimplemented a number of applications in python with similar additions. Check out pyvim, pymux, ptpython, pypager and ptterm.

And to complement that, I recommend Postgres Pager (https://github.com/okbob/pspg)

pgcli is nice

and it works great with pspg pager [https://github.com/okbob/pspg]

''\pager /usr/local/bin/pspg''

I wish there was an oracle database equivalent. The oracle SQL REPLs are both terrible but the cx_oracle python client library is decent so maybe a pgcli fork would be possible.

pspg supports MySQL and SQLite after sone configuration to their shell, might be possible for oracle as well?

one big cons of pgcli appear when we deal with long query. even it support multiline and vi mode. we still can't integrate a formatter for convenient sake.

psql also allows you to set local variables and interpolate them into SQL. Values can be assigned from query results or with shell style back ticks! Powerful and frightening!

From the manual:

  => SELECT 'hello' AS var1, 10 AS var2
  -> \gset
  => \echo :var1 :var2
  hello 10

  testdb=> \set content `cat my_file.txt`
  testdb=> INSERT INTO my_table VALUES (:'content');

While working with MySQL I can't stop wondering, why the mysql CLI interface is neglected for decades?..

It's so inconvenient compared to psql, that it makes me cringe every time I have to work with MySQL. Garbled formatting of long statements, almost non-working history, editing long statements is cumbersome, etc.

Does MySQL authors use something else to communicate with the database?

https://www.mycli.net/

can't say about mysql authors

but same as psql has its pgcli

mysql cli has its [mycli](https://www.mycli.net)

one of the great things it can do is to use mysql_config_editor encrypted credentials [https://www.mycli.net/loginpath]

I find the opposite. MySQL's CLI is very easy to use, because it uses long commands that are consistent and easy to remember, e.g. SHOW CREATE TABLE blah or GRANT ALL ON foo, rather than the cryptic letters of psql. Maybe if you use it a lot then psql makes more sense - but if you're directly connecting to the database that much, something's gone wrong, IMO.

A good tutorial for a tool I use every day. I'd add a couple of things:

1. Up/down arrows to scroll through command history

2. \timing will show the execution time of sql commands

3. \watch N allows you to repeat commands every N seconds, very handy in certain situations

Thanks a ton. I have added sections for all of them.

Up/down errors: https://github.com/tomcam/postgres#scrolling-through-the-com...

\timing: https://github.com/tomcam/postgres#timing

\tWatch https://github.com/tomcam/postgres#watch

When I first started, I was looking for a free graphical client. I asked my more seasoned coworker what he used. He said, psql.

Skeptically I tried, and after a little while, I prefer nothing else. I was already sold on vi, so it's not much surprise.

I use the \i command a lot, to run SQL commands from a file. That gives me a chance to nicely organize and indent complex queries, and tweak them as needed (with vi, of course).

That's also I do migrations. I script them in a long SQL file (surrounded by BEGIN and ROLLBACK). When it runs satisfactorily, I change ROLLBACK to COMMIT and run it again.

Been stuck with psql, all the graphical things end up being one-more-electron-app or terrible or Windows-only.

If you use a mac then postico is excellent.

I've been happy with DBVisualizer for a long time. Give it a try.

I like psql, however DBeaver ( https://dbeaver.io/ ) offers a more direct way for me for various tasks.

TablePlus is a good native macOS app, I’ve been using it for about a year now and love it.

Wonderful. Also note that psql can store settings in `~/.psqlrc`, so you can set settings there that survive sessions. For instance, in that file, you can have:

```

-- Extended display when it makes sense.

\x auto

-- Always time.

\timing

```

I also set `application_name` there. This lets others know I am connected.

Slightly tangential but I appreciate the attention to detail. Like explaining how $ means it's the command prompt—I have had non-zero classmates put `pip install xyz` in their python console and then pull their hair when that didn't work.

It makes me wonder how this sort of knowledge could be acquired in the first place. Many students studying CS haven't seen a command line before entering university, and intro classes usually encourage students to use some IDE. I've known classmates that had no idea they could run `cmake . && make && ./main` on the command line instead of clicking a button.

I feel like universities should have a really basic intro to intro cs class that just gets students familiar with the command line (CS49?).

I remember some university doing something similar as a workshop, but I can't recall which.

Over the course of classes, sometimes they do require you to use the command line but then they will just give you the commands to copy paste, without explaining what any of it does.

Back in my CS undergrad in France, we had extended bash courses.

A bit over the top in some regards, but useful nonetheless.

This is a really handy list, but misses a few of my favorites.

The first is \e to open up your default $EDITOR for the query. In the past I've actually setup sublime text to open up my last run query, be able to edit it, close and save and it'll execute. I'd imagine you could do the same with vscode, though I'm currently just configured for vim personally.

The second isn't really psql commands, but that you can setup a psqlrc - https://www.craigkerstiens.com/2013/02/13/how-i-work-with-po...

One thing I've found useful when listing tables that you can't exactly remember the name, you can use wildcards.

\dt cust*

will list all tables that start with "cust". It works with other commands as well (like \l).

Also, I like to split my terminal using tmux, and have a neovim editor in one pane, psql in the other. I just keep doing "\i file_name.sql" as I perfect the query.

I definitely agree wholeheartedly about pspg. They even have a FoxPro theme, which has a special place in my heart :).

Little funny side story: I met Marten Mickos, then CEO of MySQL, a year or two after the Sun acquisition.

He evidently did some due diligence on who I was (AWS Tech Evangelist back then), and told me that he saw some of my emails in a PostgreSQL-related mailing list. I was so impressed! I told him I saw a bright future for PostgreSQL, especially after Sun's acquisition of MySQL (which I saw as a negative for the community).

Anyway, back on topic.

I keep thinking that PostgreSQL has a great opportunity ahead: in this decade, I would bet on the rise of an Oracle-like company (hopefully, less "evil" than Oracle), based on PostgreSQL. Possibly a company that will eventually go public.

Why? Because the tech is great, the community around it is great, and because we're in need of databases more than ever. (I could rant and get lost in a much longer explanation, but I want to spare you this time :D).

Do you see it this way? Any other viewpoints?

My personal favorite psql command: \e, which is used to edit the query currently being written or the last query run in your preferred editor.

I've got a lot of mileage out of the \copy command, which copies the results of a query into a client-local file

https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-ME...

All those -h -U -p command line options are very confusing but luckily psql supports connections via URI scheme:

    psql postgresql://username:password@myhost.com:5432/databasename

Another useful command that's missing is `\o filename.txt`. The next query's output will be written to the file instead of stdout. \o again with no arguments to turn it off.

Thanks man!

Thank you so much!

Please feel free to tell me what you don't like or what needs to be added.

Tuning (or the lack of) can lead people to misconceptions. I expect pg gets friendlier over time but it is not realistic for them to configure for every use case on all hardware. A heads up and pointer to current best/acceptable practices for auto tune may help.

\pset linestyle unicode

(Full disclosure: I added this. But I think it makes the output more readable. Just retry your examples and compare them.)

Many thanks!

Hope it helps, dude!

Reply on news.ycombinator.com