Format PostgreSQL Beautifully on Dbeaver with pg_formatter External Formatter

We need on shortket Ctrl+Shift+F and the query must look like this.

Problem we have

The default formatter and compact formatter is bad in

1. Default formatter — too much line break
   SELECT * FROM my_table will have line break
2. Compact formatter — Need scroll horizontally
   INSERT INTO will be so so long

NOT READABLE !

TOO LONG !

The Official Formatter pg_formatter

I have found the sqlparser written in Python to have some problem with PostgreSQL keyword like uuid_generate_v4() so I recommend using the pg_formatter

brew install pgformatter

pg_format
-- must see the help instruction

which pg_format
-- /opt/homebrew/bin/pg_format
-- Will copy this path to Dbeaver

Dbeaver setup

Preferences > Editor > SQL Editor > Formatting

You must pick the External Formatter then add the command line

/opt/homebrew/bin/pg_format ${file}

Note that in order to call the ${file} the option use temp file MUST BE TICKED and at least at this version (2023-Jan) external formatter requires writing temp file.

Now we can format beautifully with Ctrl+Shift+F

See you next time.

Appendix: pg_formatter Flag

https://manpages.ubuntu.com/manpages/impish/man1/pg_format.1.html