Posts tagged "postgresql"

Disable pager for psql

PostgreSQL's CLI psql offers a myriad of helpful features.

For example, psql detects whenever a large result-set is returned and uses a pager to display the content.

While this is great for viewing your data, it is really inconvenient for automating tasks, as the pager needs user input to be terminated.
So, how can we circumvent / deactivate the pager?

# original, shows the pager
psql -h localhost -U postgres -c "SELECT * FROM pg_class"

# just pipe the output to `cat`
psql -h localhost -U postgres -c "SELECT * FROM pg_class" | cat

# if you are not interested in the output, you can also write to /dev/null
psql -h localhost -U postgres -c "SELECT * FROM pg_class" > /dev/null

# alternatively, you can use the environment variable `PAGER` to choose which pager should be used
PAGER=cat psql -h localhost -U postgres -c "SELECT * FROM pg_class"

# best method: completely turn off the pager
psql -h localhost -U postgres -P pager=off -c "SELECT * FROM pg_class"

Additionally, if you want to disable the pager while in interactive mode, just type \pset pager off.

Pretty Printing JSON

JSON is everywhere, but reading nested JSON without proper formatting can be a nightmare.

PostgreSQL

The function jsonb_pretty allows you to pretty print jsonb data.

\pset format unaligned
SELECT jsonb_pretty('{"name": "Lorenz", "team": {"name": "Team #1", "color", "blue"}}'::jsonb);

{
    "name": "Lorenz",
    "team": {
        "name": "Team #1",
        "color": "blue"
    }
}

Javascript

If you work with JSON data in Javascript, you surely know the function JSON.stringify. But did you know it can prettify your JSON as well?

JSON.stringify({"name": "Lorenz", "games_won": 4, "games_lost": 1}, null, 4)
                                      // number of spaces for indentation ^

{
    "name": "Lorenz",
    "games_won": 4,
    "games_lost": 1
}

Python

Python's json module adds functions to work with JSON.

>>> import json
>>> print(json.dumps({"players": [{"name": "Lorenz"}, {"name": "Philip"}]}, indent=4))

{
    "players": [
        {
            "name": "Lorenz"
        },
        {
            "name": "Philip"
        }
    ]
}

Command line using Python

You can also directly run the tool exposed by the json module from the command line:

$ echo '{"name": "Lorenz", "has_eyes": true}' | python3 -m json.tool

{
    "name": "Lorenz",
    "has_eyes": true
}

First Previous