8 ms·
Psql Tips
- alexthehurst 4y agoThis is great. psql is one of my favorite CLI tools. It’s remarkably mature and ergonomical. My personal favorites: `\e` to edit your query in $EDITOR, and `psql service=my_db` to use saved connection params.
- OJFord 4y ago> It's remarkably mature and ergonomical. The aspect of that that's hit me a few times recently is how unusual (but great) it is that it and all other (that I've needed, anyway) pg tools use the same args for connecting to a database in the same way. Once I realised, I renamed the `psql` wrapper script I'd written (takes an arg for the namespace name, looks up correct RDS host etc. for it) to `pgenv`, adding another argument for the real executable to run (i.e. `psql` is now `pgenv real_psql "$@"`) so it can also be used with dump, upgrade, vacuum...
- gen220 4y agoYou might be interested in the existence of `.pgpass` [1] and `.pg_service.conf` [2] I learned about them recently, it's been a massive quality of life improvement. [1]: https://www.postgresql.org/docs/current/libpq-pgpass.html https://www.postgresql.org/docs/current/libpq-pgpass.html [2]: https://www.postgresql.org/docs/current/libpq-pgservice.html https://www.postgresql.org/docs/current/libpq-pgservice.html
- OJFord 4y agoWith an AWS config style 'credential_process' option that'd be ideal. My script pulls the value from Terraform, which of course I could just dumo into one of those files, but it does occasionally change; it's very convenient that it just keeps on working (especially as it's also used in CI, so nothing to change and commit for it to work with new values). Thanks though, I didn't know about the latter, and I think I've only heard of pgpass from error messages and such, not actually looked into it before.
- obituary_latte 4y agoCan you expand a little on psql service usage please? I tried googling, but just came up with using ~/.pg_service.conf and setting ENV variables and the like. Thanks!
- davidw 4y agoIf you use that with Emacs' sqlup and sql formatter, you can get some very nicely lined up looking queries, if you need to share them or copy them elsewhere.
- jagged-chisel 4y agopsql - the CLI tool, not necessarily PostgreSQL tips. Just to clarify.
- mac-chaffee 4y agoBy default, psql will fetch the entire result first then print it out. This is usually fine until you need to fetch more rows than your client's RAM. To fix that, you can run "\set FETCH_COUNT 10000". Then psql will use a cursor which only fetches 10000 rows at a time, using a constant amount of RAM. This can be handy if e.g. you typically run psql on the server/container running postgres itself and you want to avoid an accidentally large query from oomkilling your database. You can set FETCH_COUNT per database and per user with ALTER: https://www.postgresql.org/docs/current/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION https://www.postgresql.org/docs/current/config-setting.html#...
- anarazel 4y agoFETCH_COUNT is a psql side setting, not something you can configure server side.
- koolba 4y agoI think GP is referring to running psql server side on the database server itself (i.e., connecting to localhost). Running a gigantic result will keep allocating memory for the result and potentially OOM the server as it’s competing for resources with the database itself right?
- anarazel 4y agoMy point is just that you can't set FETCH_COUNT with ALTER etc (as the post I was replying to suggested), because the server doesn't know anything about the parameter, as it just affects psql.
- mac-chaffee 4y agoOh yeah seems that FETCH_COUNT is special and is not a regular setting. All normal settings that you can SET can also be set per-DB and per-user: https://www.postgresql.org/docs/current/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION https://www.postgresql.org/docs/current/config-setting.html#...
- noloblo 4y agoThis is such a good idea to have a random Psql tip show up randomly is there something similar for mysql
- bt1a 4y agoOne the reasons why I like Dbeaver as a database tool!
- arustad 4y agoNice tips! Any chance there is an RSS feed?
- mbork_pl 4y agoShameless plug: I also have some psql tips on my blog, for example here: https://mbork.pl/2020-02-24_Some_psql_tips https://mbork.pl/2020-02-24_Some_psql_tips And I agree that psql is great!
- pawelduda 4y agoMy favourite tips: \e to open new/last query in $EDITOR of your choice \x to toggle extended display on/off (useful when a table has loads of columns and your screen runs out of width) If you feel like stepping out of psql, pgcli is a great drop-in replacement with autocompletion/syntax highlighting and more.
- pjungwir 4y ago\x is great but I'm even more fond of \gx, which transposes the last query you ran but leaves the setting as it was before. So it's very handy if you don't plan ahead. You can also use \gx in place of the semicolon, so you don't even need to run the query in the "wrong" mode first. Just say `SELECT * FROM wide_table \gx`.
- jrvarela56 4y agoReminded me of https://sqlfordevs.com/ https://sqlfordevs.com/ I've been following it for the last couple of months now and love the format! You get an email with a summary of the tip and link to the website (not that often, so it's not annoying).
- chaps 4y agoAnybody know of any way to make psql behave well with multiline vim mode? Hitting k while in command mode just goes to the previous command instead of the previous line in the sql you're working on. Been a thorn in my side for a while.
- jjice 4y agoDoes anyone have any neat psql usage in some automation scripts they'd care to share? Can't say I've ever used psql outside of data exports/imports and testing a quick connection.
- arp242 4y agoThis is my psqlrc; I guess I'll share that here: -- Show row count of last query in prompt. -- Gosh, why did I do it like this...? There was a reason for it and it fixes -- something, but I forgot what. select :'PROMPT1'='%/%R%x%# ' as default_prompt \gset \if :default_prompt \set PROMPT1 '(%:ROW_COUNT:)%R%# ' \endif \set QUIET \\-- Don't print welcome message etc. \set HISTFILE ~/.cache/psql-history- :DBNAME \\-- Keep history per database \set HISTSIZE -1 \\-- Infinite history \set HISTCONTROL ignoredups \\-- Don't store duplicates in history \set PROMPT2 '%R%# ' \\-- No database name in the line continuation prompt. \set COMP_KEYWORD_CASE lower \\-- Complete keywords to lower case. \pset linestyle unicode \\-- Nicely formatted tables. \pset footer off \\-- Don't display "(n rows)" at the end of the table. \pset null 'NULL' \\-- Display null values as NULL \timing on \\-- Show query timings \set pretty '\\pset numericlocale' \\-- Toggle between thousands separators in numbers Storing history per-database is really useful if you regularly connect to different unrelated databases. I distinctly remember setting the prompt in such a funky way for a very specific reason; it took me a while to find a good solution. But for the life of me I can't remember why. Set the PSQLRC environment variable to store it somewhere else (e.g. ~/.config/psqlrc).
- 1ba9115454 4y agoI didn't know you could do this. Thanks.
- niels_bom 4y ago(I don’t use databases that often) I really like pgcli (1) over psql. pgcli has really nice autocompletion and just looks good. https://www.pgcli.com/ https://www.pgcli.com/
- noloblo 4y agoRunning auto vacuum periodically keeps the Psql instance from crashing