6 ms·
Psql command line tutorial and cheat sheet
- abhinav22 6y agoMany thanks!
- tomcam 6y agoHope it helps, dude!
- david927 6y agoThank you so much!
- tomcam 6y agoPlease feel free to tell me what you don't like or what needs to be added.
- tejtm 6y agoTuning (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.
- rleigh 6y ago\pset linestyle unicode (Full disclosure: I added this. But I think it makes the output more readable. Just retry your examples and compare them.)
- suprth 6y agoThanks man!
- why-el 6y agoWonderful. 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.
- cgh 6y agoA 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
- tomcam 6y agoThanks a ton. I have added sections for all of them. Up/down errors: https://github.com/tomcam/postgres#scrolling-through-the-command-history https://github.com/tomcam/postgres#scrolling-through-the-com... \timing: https://github.com/tomcam/postgres#timing https://github.com/tomcam/postgres#timing \tWatch https://github.com/tomcam/postgres#watch https://github.com/tomcam/postgres#watch
- rarrrrrr 6y agopsql 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');
- NAR8789 6y agoTo complement this, I recommend installing `pgcli`[https://www.pgcli.com/ 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.
- russelwilson 6y agoI second this motion.
- Cieplak 6y agoI went back and forth between psql and pgcli for several years, until I realized that pgcli tab-completes JOIN conditions.
- jessedhillon 6y agoPostgres 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.
- EQYV 6y agoThanks! Just switched to this for a project I'm working on.
- qwertox 6y agoA drop-down autocomplete in a console? First time I've seen such a thing. Thanks for pointing out this tool.
- djeiasbsbo 6y agoThat'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.
- deleted 6y ago[deleted]
- anticodon 6y agoWhile 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?
- mmerlin 6y agohttps://www.mycli.net/ https://www.mycli.net/
- resheku 6y agocan't say about mysql authors but same as psql has its pgcli mysql cli has its [mycli](https://www.mycli.net 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 https://www.mycli.net/loginpath]
- lmm 6y agoI 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.
- combatentropy 6y agoWhen 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.
- Can_Not 6y agoBeen stuck with psql, all the graphical things end up being one-more-electron-app or terrible or Windows-only.
- nicoburns 6y agoIf you use a mac then postico is excellent.
- psychstudio 6y agoI've been happy with DBVisualizer for a long time. Give it a try.
- natmaka 6y agoI like psql, however DBeaver ( https://dbeaver.io/ https://dbeaver.io/ ) offers a more direct way for me for various tasks.
- bsagdiyev 6y agoTablePlus is a good native macOS app, I’ve been using it for about a year now and love it.
- craigkerstiens 6y agoThis 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-postgres/ https://www.craigkerstiens.com/2013/02/13/how-i-work-with-po...
- yen223 6y agoI'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-META-COMMANDS-COPY https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-ME...
- jasonl99 6y agoOne 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 :).
- Freaken 6y agoMy personal favorite psql command: \e, which is used to edit the query currently being written or the last query run in your preferred editor.
- clashmeifyoucan 6y agoSlightly 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.
- siraben 6y agoIt 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.
- clashmeifyoucan 6y agoI 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.
- glabu 6y agoBack in my CS undergrad in France, we had extended bash courses. A bit over the top in some regards, but useful nonetheless.
- _Qeomash_ 6y agoAnother 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.
- coffee667 6y agoAll 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
- simonebrunozzi 6y agoLittle 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?