[HN Gopher] Psql Tips
___________________________________________________________________
Psql Tips
Author : todsacerdoti
Score : 165 points
Date : 2023-02-23 12:40 UTC (10 hours ago)
(HTM) web link (psql-tips.org)
(TXT) w3m dump (psql-tips.org)
| noloblo wrote:
| This is such a good idea to have a random Psql tip show up
| randomly is there something similar for mysql
| bt1a wrote:
| One the reasons why I like Dbeaver as a database tool!
| mbork_pl wrote:
| 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!
| arustad wrote:
| Nice tips! Any chance there is an RSS feed?
| arp242 wrote:
| 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).
| 1ba9115454 wrote:
| I didn't know you could do this. Thanks.
| mac-chaffee wrote:
| 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#...
| anarazel wrote:
| FETCH_COUNT is a psql side setting, not something you can
| configure server side.
| koolba wrote:
| 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?
| anarazel wrote:
| 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.
| mac-chaffee wrote:
| 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#...
| anarazel wrote:
| It's just a clientside knob for psql. You can do something
| like it in any client.
| alexthehurst wrote:
| 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.
| obituary_latte wrote:
| 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!
| OJFord wrote:
| > 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 wrote:
| 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
| OJFord wrote:
| 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.
| davidw wrote:
| 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.
| niels_bom wrote:
| (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/
| tombh wrote:
| 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.
| nerdo wrote:
| It's not a wrapper, uses psycopg.
| jagged-chisel wrote:
| psql - the CLI tool, not necessarily PostgreSQL tips. Just to
| clarify.
| jrvarela56 wrote:
| 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).
| pawelduda wrote:
| 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.
| pjungwir wrote:
| \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`.
| napsterbr wrote:
| Appreciate the tip. \x is pretty much my default mode and I
| always forget to set it on the first query.
| arp242 wrote:
| You can add it to ~/.psqlrc to always enable it. Use "psql
| -X" to skip loading that file (useful for scripts).
| chaps wrote:
| 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.
| jjice wrote:
| 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.
___________________________________________________________________
(page generated 2023-02-23 23:01 UTC)