Psql Tips(psql-tips.org)
psql-tips.org
Psql Tips
https://psql-tips.org/
30 comments
FETCH_COUNT is a psql side setting, not something you can configure server side.
Oh 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#...
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#...
It's just a clientside knob for psql. You can do something like it in any client.
I 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?
My 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.
This is my psqlrc; I guess I'll share that here:
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).
-- 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
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).
I didn't know you could do this. Thanks.
This 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.
My personal favorites: `\e` to edit your query in $EDITOR, and `psql service=my_db` to use saved connection params.
> 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...
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...
You 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
[2]: https://www.postgresql.org/docs/current/libpq-pgservice.html
I learned about them recently, it's been a massive quality of life improvement.
[1]: https://www.postgresql.org/docs/current/libpq-pgpass.html
[2]: https://www.postgresql.org/docs/current/libpq-pgservice.html
With 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.
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.
Can 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!
If 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.
(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/
Reminded me of 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).
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).
psql - the CLI tool, not necessarily PostgreSQL tips. Just to clarify.
My 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)
\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`.
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`.
Shameless plug: I also have some psql tips on my blog, for example here: https://mbork.pl/2020-02-24_Some_psql_tips And I agree that psql is great!
Anybody 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.
Does 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.
Running auto vacuum periodically keeps the Psql instance from crashing
Nice tips! Any chance there is an RSS feed?
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#...