dbi Blog

pgFormatter is a command-line tool used to format SQL code for PostgreSQL databases. It helps improve code readability and maintainability by applying consistent indentation and formatting rules to your SQL queries. This guide will walk you through the process of installing and using pgFormatter on a Linux system, specifically openSUSE.

I. Installation

For this guide, I will install pgFormatter using the sources. The latest version at the time I wrote this blog was 5.5.

14:33:06 postgres@localhost:/home/postgres/ [PG16] version=5.5
14:33:32 postgres@localhost:/home/postgres/ [PG16] wget https://github.com/darold/pgFormatter/archive/refs/tags/v${version}.tar.gz
14:34:03 postgres@localhost:/home/postgres/ [PG16] tar xzf v${version}.tar.gz
14:34:17 postgres@localhost:/home/postgres/ [PG16] cd pgFormatter-${version}/
14:34:41 postgres@localhost:/home/postgres/pgFormatter-5.5/ [PG16] perl Makefile.PL
14:38:12 postgres@localhost:/home/postgres/pgFormatter-5.5/ [PG16] make && sudo make install
14:40:27 postgres@localhost:/home/postgres/ [PG16] cd ../ && rm -rf v${version}.tar.gz && rm -rf pgFormatter-${version}

On OpenSuse, I had an issue to run the script pg_format from anywhere, you can fix it like that:

14:27:34 postgres@localhost:/home/postgres/ [PG16] vi .bashrc
export PATH="$PATH:/usr/bin/pg_format"

14:28:26 postgres@localhost:/home/postgres/ [PG16] source ~/.bashrc

14:28:34 postgres@localhost:/home/postgres/ [PG16] pg_format --help
Usage: pg_format [options] file.sql
    PostgreSQL SQL queries and PL/PGSQL code beautifier.…
…
…

2. Basic usage

You can format SQL directly from the command line.
Here is an example query that is badly formatted:

SELECT first_name,last_name,
SUM(order_total) AS total_spent,
CASE WHEN gender = 'Male' THEN 'M' WHEN gender = 'Female' THEN 'F'
ELSE 'Other'
END AS simplified_gender FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE registration_date BETWEEN '2022-01-01' AND '2023-01-01'
AND (country = 'USA' OR country = 'Canada')
GROUP BY gender, first_name, last_name HAVING total_spent > 1000 ORDER BY total_spent DESC;

You can format it by following theses next steps:
run pg_format command

15:00:38 postgres@localhost:/home/postgres/ [PG16] pg_format

Copy paste your query.
Press ctrl+d on your keyboard, and then you get the result:

SELECT
    first_name,
    last_name,
    SUM(order_total) AS total_spent,
    CASE WHEN gender = 'Male' THEN
        'M'
    WHEN gender = 'Female' THEN
        'F'
    ELSE
        'Other'
    END AS simplified_gender
FROM
    customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE
    registration_date BETWEEN '2022-01-01' AND '2023-01-01'
    AND (country = 'USA'
        OR country = 'Canada')
GROUP BY
    gender,
    first_name,
    last_name
HAVING
    total_spent > 1000
ORDER BY
    total_spent DESC;

You can also use pg_formatter to format queries inside a file.
First, create a sql file with our previous unformatted query.

15:27:30 postgres@localhost:/home/postgres/ [PG16] echo "SELECT first_name,last_name,
> SUM(order_total) AS total_spent,
> CASE WHEN gender = 'Male' THEN 'M' WHEN gender = 'Female' THEN 'F'
> ELSE 'Other'
> END AS simplified_gender FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
> WHERE registration_date BETWEEN '2022-01-01' AND '2023-01-01'
> AND (country = 'USA' OR country = 'Canada')
> GROUP BY gender, first_name, last_name HAVING total_spent > 1000 ORDER BY total_spent DESC;" > unformatted_sql_query.sql

2.1. cat content of a file directly formatted

15:34:40 postgres@localhost:/home/postgres/ [PG16] cat unformatted_sql_query.sql | pg_format
SELECT
    first_name,
    last_name,
    SUM(order_total) AS total_spent,
    CASE WHEN gender = 'Male' THEN
        'M'
    WHEN gender = 'Female' THEN
        'F'
    ELSE
        'Other'
    END AS simplified_gender
FROM
    customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE
    registration_date BETWEEN '2022-01-01' AND '2023-01-01'
    AND (country = 'USA'
        OR country = 'Canada')
GROUP BY
    gender,
    first_name,
    last_name
HAVING
    total_spent > 1000
ORDER BY
    total_spent DESC;

2.2. Format the content and output it in a new file

15:36:05 postgres@localhost:/home/postgres/ [PG16] pg_format  -o formated_query.sql unformatted_sql_query.sql
15:37:28 postgres@localhost:/home/postgres/ [PG16] cat formated_query.sql
SELECT
    first_name,
    last_name,
    SUM(order_total) AS total_spent,
    CASE WHEN gender = 'Male' THEN
        'M'
    WHEN gender = 'Female' THEN
        'F'
    ELSE
        'Other'
    END AS simplified_gender
FROM
    customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE
    registration_date BETWEEN '2022-01-01' AND '2023-01-01'
    AND (country = 'USA'
        OR country = 'Canada')
GROUP BY
    gender,
    first_name,
    last_name
HAVING
    total_spent > 1000
ORDER BY
    total_spent DESC;

3. Advanced usage

pgFormatter allows you to configure various formatting options according to your preferences. You can directly pass options to the pg_format command or you can configure pgFormatter by creating a configuration file named . pg_format in your home directory ($HOME/.pg_format) or in the directory where you’re running the command.

3.1. Use options with pg_formatter command

You can list available options by running the commands pg_format –help or you can find them on the Github of the project. Let’s use the file unformatted_sql_query.sql for our example.
We are going to use the options -a and -b.
-a: Make queries more secure by concealing all literal information. This is helpful for safeguarding sensitive data prior to formatting.
-b: When making a list of parameters, begin with a comma

15:47:10 postgres@localhost:/home/postgres/ [PG16] cat unformatted_sql_query.sql | pg_format -b -a
SELECT
    first_name
    , last_name
    , SUM(order_total) AS total_spent
    , CASE WHEN gender = 'AYbHbbLmMP' THEN
        'krP5UwG1JX'
    WHEN gender = 'pSRVKMIWBU' THEN
        'e7lslNpxTK'
    ELSE
        'Yi5aXsXLre'
    END AS simplified_gender
FROM
    customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE
    registration_date BETWEEN '747vADdsUQ' AND 'UZ_iVYEk40'
    AND (country = 'iW14Pzl_Dm'
        OR country = '2rmPyVcZ7s')
GROUP BY
    gender
    , first_name
    , last_name
HAVING
    total_spent > 1000
ORDER BY
    total_spent DESC;

If you compare the results to what we have inside the file formated_query.sql, you can see the differences.

3.2. Format the content by editing the config file

Create the necessary file and edit it.

15:49:07 postgres@localhost:/home/postgres/ [PG16] vi .pg_format
comma=start

Now, we are going to format the content of our file unformatted_sql_query.sql without passing formatting options to the command.

15:53:40 postgres@localhost:/home/postgres/ [PG16] pg_format -n unformatted_sql_query.sql
SELECT
    first_name
    , last_name
    , SUM(order_total) AS total_spent
    , CASE WHEN gender = 'Male' THEN
        'M'
    WHEN gender = 'Female' THEN
        'F'
    ELSE
        'Other'
    END AS simplified_gender
FROM
    customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE
    registration_date BETWEEN '2022-01-01' AND '2023-01-01'
    AND (country = 'USA'
        OR country = 'Canada')
GROUP BY
    gender
    , first_name
    , last_name
HAVING
    total_spent > 1000
ORDER BY
    total_spent DESC;

Tada ! The coma=start options has been taken into consideration.

4. Integration with Editors

You can integrate pgFormatter with text editors to automatically format SQL code. We are going to use vim as an example.
Add the following line to your .vimrc configuration file.

16:00:44 postgres@localhost:/home/postgres/ [PG16]  echo "au FileType sql setl formatprg=/usr/bin/pg_format\ - " >> .vimrc

If you moved your binaries to /usr/local/bin/pg_format, don’t forget to change the path.

Once done, you can format the content inside a file with vim.

vim unformatted_sql_query.sql

### press ESC and then type gqG to format the entire file ###

5. Conclusion

pgFormatter is a valuable tool for enhancing the readability and maintainability of your SQL code. By following this guide, you’ve learned how to install pgFormatter on openSUSE, format SQL files and commands, configure advanced settings, and even integrate it with your favorite text editor. This will help you produce cleaner, more consistent SQL code in your PostgreSQL projects. Again thanks to the developers and don’t forget to check the Github of the pgFormatter project to learn more about it.

Useful links

https://github.com/darold/pgFormatter

Post Views: 1,075

Guide to Install and Use pgFormatter on Linux (openSUSE)

I. Installation

2. Basic usage

2.1. cat content of a file directly formatted

2.2. Format the content and output it in a new file

3. Advanced usage

3.1. Use options with pg_formatter command

3.2. Format the content by editing the config file

4. Integration with Editors

5. Conclusion

Useful links

Joan Frey

Leave a Reply:

Related blog articles

I. Installation

2. Basic usage

2.1. cat content of a file directly formatted

2.2. Format the content and output it in a new file

3. Advanced usage

3.1. Use options with pg_formatter command

3.2. Format the content by editing the config file

4. Integration with Editors

5. Conclusion

Useful links

Joan Frey

Leave a Reply:

Related blog articles

Oracle 23ai – True Cache

PosgreSQL + SQL Server = WiltonDB – Part 2 – Database Management

Plotting SQL Server Performance counters with Python

CloudNativePG – Configuring the PostgreSQL instance