Query tool
The Query Tool enables you to execute arbitrary SQL commands.
The upper part of the Query Tool contains the SQL Editor
where you type your commands. You may read the query from a file, or
write it out to a file. When writing a file, the encoding of the file
is determined by the file suffix: if it is *.sql, a 8 bit local
character set is used. If *.usql is selected, the file will be
written in UTF-8, which enables the storage of virtually all
characters used throughout the world, according to the coding rules
of the Unicode Consortium. If
you select *.*, the setting from the read/write unicode option is used.
The edit entry window also contains autocompletion code to help you
write queries. To use autocomplete, simply start typing your query
and then press Control+Space to see a list of possible object names to
insert. For example, type “*SELECT * FROM* ” (without the quotes, but
with the trailing space), and then hit Control and Space together to see
a popup menu of autocomplete options. The autocomplete system is based on
code from psql, the PostgreSQL command line interpreter and will
generally be able to offer autocomplete options in the same places that
it can in psql.
The editor also offers a number of features for helping with code formatting:
- The auto-indent feature will automatically indent text to the same depth as the
previous line when you press return.
- Block indent text by selecting two or more lines and pressing the Tab key.
- Block outdent text that has been indented using the current tab settings using Shift+Tab.
- Comment out SQL by selecting some text and pressing the Control+K.
- Uncomment SQL by selecting some text starting with a comment and pressing Control+Shift+K.
- Shift the selected text to upper case by pressing Control+U.
- Shift the selected text to lower case by pressing Control+Shift+U.
If you prefer to build your queries graphically, you can use the
Graphical Query builder to generate the SQL for you.
To execute a query, select Execute from the Query menu,
press the execute toolbar button, or press the F5 function
key. The complete contents of the window
will be sent to the database server, which executes it. You may also
execute just a part of the text, by selecting only the text that you
want the server to execute.
Options on the Query menu allow you to control COMMIT and ROLLBACK behavior for transactions:
- Check the box next to Auto-Rollback to instruct the server to automatically roll back a transaction if an error occurs during the transaction.
- Check the box next to Auto-Commit to instruct the server to automatically commit each transaction. Any changes made by the transaction will be visible to others, and are guaranteed to be durable in the event of a crash. By default, auto-commit behavior is enabled.
Explain from the Query menu, or F7 function key will execute the
EXPLAIN command. The database server will analyze the query that’s
sent to it, and will return the results.
The result is displayed as text in the Data Output page, and graphically
visualized on the Explain page. This enables you to find out how the
query is parsed, optimized and executed. You can modify the
degree of inspection by changing the Explain options for this in the
Query menu. Please note that “EXPLAIN VERBOSE” can not be displayed graphically.
In case the query you sent to the server using the Execute or
Explain command takes longer than you expect, and you would like to
abort the execution, you can select Cancel from the Query menu, press
the Cancel toolbar button or use Alt-Break function key to abort the
execution.
You can run pgScript scripts by selecting Execute pgScript from the Query menu instead of Execute, or
you press the Execute pgScript toolbar button, or you press the F6 function
key. The complete contents of the edit entry window
will be sent to the pgScript engine, which interprets it. pgScript scripts are
composed of regular SQL commands but add some enhancements:
- Control-of-flow language (
IF
and WHILE
structures)
- Local variables (
SET @VARIABLE = 5
)
- Random data generators (
INTEGER(10, 20)
or REFERENCE(table, column)
)
For more information on the pgScript language, please look at the
pgScript scripting language reference.
If you want to have help about a SQL command you want to execute,
you can mark a SQL keyword and select SQL Help from the Help menu,
the SQL Help toolbar button or simply press the F1 key. pgAdmin III
will try to locate the appropriate information in the PostgreSQL
documentation for you.
The result of the database server execution will be displayed in
the lower part of the Query Tool. If the last command in the chain of
SQL command sent to the server was as command returning a result set,
this will be shown on the Data Output page. All rowsets from previous
commands will be discarded.
To save the data in the Data Output page to a file, you can use
the Export dialog.
Information about all commands just executed will go to the
Messages page. The History page will remember all commands executed
and the results from this, until you use Clear History from the Query
menu to clear the window. If you want to retain the history for later
inspection, you can save the contents of the History page to a file
using the Save history option from the Query menu.
The status line will show how long the last query took to
complete. If a dataset was returned, not only the elapsed time for
server execution is displayed, but also the time to retrieve the data
about your current selection.
In the toolbar combobox you can quickly change your database connection from
one database to another, without launching another instance of the query tool.
Initially, only one database will be available, but by selecting <new connection> from
the combobox, you can add another connection to it.
In the options dialog, you
can specify a default limit for the rowset size to retrieve. By
default, this value will be 100. If the number of rows to retrieve
from the server exceeds this value, a message box will appear asking
what to do to prevent retrieval of an unexpected high amount of data.
You may decide to retrieve just the first rows, as configured with
the max rows setting, or retrieving the complete rowset regardless of
the setting, or abort the query, effectively retrieving zero rows.
If you have queries that you execute often, you can add these to the
favourites menu, and have them automatically put in the buffer when you
select them from the menu. The Manage Favourites menu option may be used
to organise your favourites. Alternatively, you can store them as
Query Tool Macros.
The Query Tool also includes a powerful Find and Replace tool. In addition
to offering the normal options found in most tools, a Regular Expression search
mode is included which allows you to perform extremely powerful search
(and replace) operations. Unless your copy of pgAdmin was built against a
non-standard build of wxWidgets, a built in version of Henry Spencer’s regular
expression library is used based on the 1003.2 spec and some (not quite all) of the
Perl5 extensions.
For more details of the regular expression syntax offered, please see the
wxWidgets documentation.
Contents: