Psql Tips | Hacker News Reader

Psql Tips

psql-tips.org

189 pointstodsacerdoti30 comments

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#...

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#...

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:

  -- 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).

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.

> 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...

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

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 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/

Me too. From what I understand it is essentially a wrapper around `psql`, so all the advice here and in the website still apply to it as well.

It’s not a wrapper, uses psycopg.

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).

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)

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`.

Appreciate the tip. \x is pretty much my default mode and I always forget to set it on the first query.

You can add it to ~/.psqlrc to always enable it. Use "psql -X" to skip loading that file (useful for scripts).

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.

This is such a good idea to have a random Psql tip show up randomly is there something similar for mysql

One the reasons why I like Dbeaver as a database tool!

Running auto vacuum periodically keeps the Psql instance from crashing

Nice tips! Any chance there is an RSS feed?

Reply on news.ycombinator.com