Working with SQL Queries

Custom database queries can be executed from the SQL Query view in Postico. You can get to the SQL Query View by clicking on a query file in the sidebar. There's also a keyboard shortcut (⇧⌘T) to navigate to the query view.

Executing SQL Statements

The query view highlights the current statement in light grey. Use the command "Execute Query" in the "Connection" menu or press ⌘↩︎ to execute the current statement (or the current selection, if any). The corresponding results are displayed below the text view.

You can execute multiple statements at once by selecting them. They will be executed in an implicit transaction, so if one of the statements fails, all changes to the database will be rolled back.

When you execute multiple statements, Postico will display the first result. You can switch between results using a popup menu button at the bottom left.

Use the command "Execute All Queries" in the "Connection" menu or ⌥⌘↩︎ to execute all statements at once.

To clear the result area, use the command "Clear Results" in the "Connection" menu or press ⌘K.

Autocomplete

There are two autocomplete modes available in the SQL query view.

Tab Completion: Press Tab, and Postico automatically tries to complete the word you are typing. When multiple completions are possible, you hear a beep. Press Tab again to display a list of possible completions in a popup window. We tried to make the behaviour similar to tab completion in command-line tools like psql and bash.
Show Completions: Press Ctrl-Space or ESC, and Postico shows a list of all possible completions in a popup window, including additional matches related to the word you have typed. This mode can even be used if there is no word (e.g. you are trying to "complete" a whitespace character). You can also trigger autocomplete using the command "Show Completions" in the "Edit" menu.

In the autocompletion window, use the arrow keys to select a completion, then hit the return key to confirm.

Multiple matches of the same suggestion will be grouped by type, use the left and right arrow keys to expand and collapse grouped suggestions.

Indentation

Multiple selected lines can be indented at once.

Use the command "Shift Right" in the "Edit" menu or press ⇥ or ⌘] to shift the selected line(s) to the right (increase indentation).

Use the command "Shift Left" in the "Edit" menu or press ⇧⇥ or ⌘[ to shift the selected line(s) to the left (decrease indentation).

Autoformat

Postico 2 includes pgFormatter for formatting SQL Queries. The default shortcut is Ctrl-F.

The query is formatted locally and is not sent to a web service.

To customize the behavior of pgFormatter, you can write settings in the ~/.pg_format file.

Looking up PostgreSQL commands

If you have Dash installed, you can use the command "Look Up In Dash" in the context menu or press Ctrl-H to look up the current word (or selection) in Dash.