By 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#...
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.
-- 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).
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...
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.
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'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).
\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.
\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`.
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.
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#...