Pgadmin 4 как писать запросы sql

Pgadmin 4 как писать запросы sql

Как правило, работа с базой данных осуществляется с помощью специального языка запросов — SQL. Рассмотрим, как выполнять простейшие SQL-запросы к базе данных в pgAdmin.

К примеру, возьмем базу данных test1, которая была создана в прошлой теме (или создадим новую) и добавим в нее таблицу и некоторые начальные данные. Для этого нажмем в правой части окна pgAdmin на базу данных правой кнопкой мыши и в появившемся контекстном меню выберем пункт Query Tool :

После этого в центральной части программы откроется поле для ввода кода SQL. Введем следующий набор выражений:

Фактически весь код разбивается на две части. Первая часть — инструкция CREATE TABLE , которая создает таблицу users с тремя столбцами Id, Name и Age. И вторая часть — инструкция INSERT , которая добавляет в таблицу одну строку.

Чтобы выполнить данные инструкции, нажмем над кодом в панели инструментов на стрелочку. И после этого в выбранную базу данных (test1) будет добавлена таблица users, в которую будет добавлена одна строка.

Впоследствии подобным образом будет выполняться любой другой код SQL к базе данных. Также выбирается нужная база данных, выбирается параметр Query Tool, и далее в поле ввода вводится код SQL, который затем выполняется.

Следует отметить, что для каждой таблицы определяется схема. По умолчанию это схема «public». Поэтому чтобы найти таблицу, нам надо обратиться к узлу базы данных, раскрыть его, далее выбрать подузел Schemas , в нем подузел public (название схема), и далее в нем подузел Tables , который представляет все таблицы, ассоциированные со схемой public:

Теперь получим данные из таблицы, которые были добавлены при ее создании. Для этого выполним следующий код:

И внизу программы в поле Data Output мы увидим в табличном представлении те данные, которые ранее были добавлены.

PostgreSQL: Up and Running, 3rd Edition by Regina O. Obe, Leo S. Hsu

Get full access to PostgreSQL: Up and Running, 3rd Edition and 60K+ other titles, with a free 10-day trial of O’Reilly.

There are also live events, courses curated by job role, and more.

Chapter 4. Using pgAdmin

pgAdmin4 version 1.6 is the current rendition of the tried-and-true graphical administration tool for PostgreSQL. It is a complete rewrite of the predecessor pgAdmin3. Some features of pgAdmin3 have not been ported to pgAdmin4, though they may be in the future. In this chapter we’ll focus on what’s available in pgAdmin4. Much of the functionality you will find in pgAdmin4 was present in pgAdmin3, so this discussion will be valuable even if you are still using pgAdmin3. We will also cover some popular features of pgAdmin3 not yet ported to pgAdmin4. For the rest of this chapter, we’ll simply refer to both as pgAdmin, and only make distinguishing version notes where the functionality is different.

Most of the key changes thus far with pgAdmin4 compared to pgAdmin3 is that pgAdmin4 better supports the new 9.6 and 10 constructs including the ability to run in a server or desktop mode; an improved query results pane with ability to edit records and also select noncontiguous rows; and improved performance. If you are using Windows, make sure to use pgAdmin4 1.6 or above. Prior pgAdmin4 versions had performance issues on Windows when running in desktop mode.

Although pgAdmin has shortcomings, we are always encouraged by not only how quickly bugs are fixed, but also how quickly new features are added. Because the PostgreSQL developers position pgAdmin as the most commonly used graphical-administration tool for PostgreSQL and it is packaged with many binary distributions of PostgreSQL, the developers have taken on the responsibility of keeping pgAdmin always in sync with the latest PostgreSQL releases. If a new release of PostgreSQL introduces new features, you can count on the latest pgAdmin to let you manage it. If you’re new to PostgreSQL, you should definitely start with pgAdmin before exploring other tools.

Getting Started

pgAdmin4 comes packaged with many distributions. The BigSQL and EDB distributions from PostgreSQL 9.6 on include pgAdmin4 as an option. Note if you have a need for pgAdmin3 for PostgreSQL 9.6+, you’ll want to use the BigSQL pgAdmin3 LTS, which has been patched to handle versions 9.6 and 10. pgAdmin3 LTS is installable via the BigSQL package manager. After version 9.5, the EDB package only includes pgAdmin4. The pgAdmin group will no longer be making updates or enhancements to pgAdmin3.

If you are installing pgAdmin without PostgreSQL, you can download pgAdmin from pgadmin.org. While on the site, you can opt to peruse one of the guides introducing pgAdmin. The tool is well-organized and, for the most part, guides itself quite well. Adventurous users can always try beta and alpha releases of pgAdmin. Your help in testing would be greatly appreciated by the PostgreSQL community.

Overview of Features

To whet your appetite, here’s a list of our favorite goodies in pgAdmin. More are listed in pgAdmin Features:

Server and Desktop mode

pgAdmin4 can be installed in desktop mode or as a web server WSGI application. pgAdmin3 was a desktop-only application.

Graphical explain for your queries

This awesome feature offers pictorial insight into what the query planner is thinking. While verbose text-based planner output still has its place, a graphical explain provides a more digestible bird’s-eye view.

pgAdmin ultimately interacts with PostgreSQL via SQL, and it’s not shy about letting you see the generated SQL. When you use the graphical interface to make changes to your database, pgAdmin automatically displays, in an SQL pane, the underlying SQL that will perform the tasks. For novices, studying the generated SQL is a superb learning opportunity. For pros, taking advantage of the generated SQL is a great timesaver.

GUI editor for configuration files such as postgresql.conf and pg_hba.conf

You no longer need to dig around for the files and use another editor. This is currently only present in pgAdmin3, and to use it, you also need to install the pgadmin extension in the database called postgres .

Data export and import

pgAdmin can easily export query results as a CSV file or other delimited format and import such files as well. pgAdmin3 can even export results as HTML, providing you with a turnkey reporting engine, albeit a bit crude.

Backup and restore wizard

Can’t remember the myriad commands and switches to perform a backup or restore using pg_restore and pg_dump? pgAdmin has a nice interface that lets you selectively back up and restore databases, schemas, single tables, and globals. You can view and copy the underlying pg_dump or pg_restore command that pgAdmin used in the Message tab.

This timesaver allows you to change privileges on many database objects in one fell swoop.

This is a quick-and-dirty way to run scripts that don’t have to complete as transactions. With this you can execute loops that commit on each iteration, unlike functions that require all steps to be completed before the work is committed. Unfortunately, you cannot use this engine outside of pgAdmin and it is currently only available in pgAdmin3 (not 4).

SQL Editor Autocomplete feature

To trigger the autocomplete popup use CTRL-Space. The autocomplete feature is improved in pgAdmin4.

We’ll devote an entire section to this cross-platform job scheduling agent. pgAdmin provides a cool interface to it.

Connecting to a PostgreSQL Server

Connecting to a PostgreSQL server with pgAdmin is straightforward. The General and Connection tabs are shown in Figure 4-1.

Figure 4-1. pgAdmin4 register server connection dialog

Navigating pgAdmin

The tree layout of pgAdmin is intuitive to follow but does engender some possible anxiety, because it starts off by showing you every esoteric object found in the database. You can pare down the tree display by going into the Browser section of Preferences and deselecting objects that you would rather not have to stare at every time you use pgAdmin. To declutter the browse tree sections, go to Files→Preferences→Browser→Nodes. You will see the screen shown in Figure 4-2.

Figure 4-2. Hide or unhide database objects in the pgAdmin4 browse tree

If you select Show System Objects in the Display section, you’ll see the guts of your server: internal functions, system tables, hidden columns in tables, and so forth. You will also see the metadata stored in the PostgreSQL system catalogs: information_schema catalog and the pg_catalog . information_schema is an ANSI SQL standard catalog found in other databases such as MySQL and SQL Server. You may recognize some of the tables and columns from working with other database products.

pgAdmin Features

pgAdmin is chock full of goodies. We don’t have the space to bring them all to light, so we’ll just highlight the features that many use on a regular basis.

Autogenerating Queries from Table Definitions

pgAdmin has this menu option that will autogenerate a template for SELECT, INSERT, and UPDATE statements from a table definition. You access this feature by right-clicking the table and accessing the SCRIPTS context menu option as shown in Figure 4-3.

Figure 4-3. Table Scripts menu

The “SELECT Script” option is particularly handy because it will create a query that lists all the columns in the table. If you have a lot of columns in a table and want to select a large subset but not all columns, this is a great timesaver. You can remove columns you don’t need in your query from the autogenerated statement.

Accessing psql from pgAdmin3

Although pgAdmin is a great tool, psql does a better job in a few cases. One of them is the execution of very large SQL files, such as those created by pg_dump and other dump tools. You can easily jump to psql from pgAdmin3, but this feature is not available in pgAdmin4. Click the plugin menu, as shown in Figure 4-4, and then click PSQL Console. This opens a psql session connected to the database you are currently connected to in pgAdmin. You can then use the \cd and \i commands to change directory and run the SQL file.

Figure 4-4. psql plugin

Because this feature relies on a database connection, you’ll see it disabled until you’re connected to a database.

Editing postgresql.conf and pg_hba.conf from pgAdmin3

You can edit configuration files directly from pgAdmin, provided that you installed the adminpack extension on your server. PostgreSQL one-click installers generally create the adminpack extension. If it’s present, you should see the Server Configuration menu enabled, as shown in Figure 4-5.

Figure 4-5. PgAdmin3 configuration file editor

If the menu is grayed out and you are connected to a PostgreSQL server, either you don’t have the adminpack installed on that server or you are not logged in as a superuser. To install the adminpack run the SQL statement CREATE EXTENSION adminpack; or use the graphical interface for installing extensions, as shown in Figure 4-6. Disconnect from the server and reconnect; you should see the menu enabled.

Figure 4-6. Installing extensions using pgAdmin4

Creating Database Assets and Setting Privileges

pgAdmin lets you create all kinds of database assets and assign privileges.

Creating databases and other database assets

Creating a new database in pgAdmin is easy. Just right-click the database section of the tree and choose New Database, as shown in Figure 4-7. The Definition tab provides a drop-down menu for you to select a template database, similar to what we did in “Template Databases”.

Figure 4-7. Creating a new database in pgAdmin4

Follow the same steps to create roles, schemas, and other objects. Each will have its own relevant set of tabs for you to specify additional attributes.

Privilege management

To manage the privileges of database assets, nothing beats the pgAdmin Grant Wizard, which you access from the Tools→Grant Wizard menu of pgAdmin. If you are interested in granting permissions only for objects in a specific schema, right-click the schema and choose “Grant Wizard.” The list will be filtered to just objects in the schema. As with many other features, this option is grayed out unless you are connected to a database. It’s also sensitive to the location in the tree you are on. For example, to set privileges for items in the census schema, select the schema and then choose Grant Wizard. The Grant Wizard screen is shown in Figure 4-8. You can then select all or some of the items and switch to the Privileges tab to set the roles and privileges you want to grant.

Figure 4-8. Grant Wizard in pgAdmin4

More often than setting privileges on existing objects, you may want to set default privileges for new objects in a schema or database. To do so, right-click the schema or database, select Properties, and then go to the Default Privileges tab, as shown in Figure 4-9.

Figure 4-9. Granting default privileges in pgAdmin4

When setting privileges for a schema, make sure to also set the usage privilege on the schema to the groups you will be giving access to.

Import and Export

Like psql, pgAdmin allows you to import and export text files.

Importing files

The import/export feature is really a wrapper around the psql \copy command and requires the table that will receive the data to exist already. In order to import data, right-click the table you want to import/export data to. Figure 4-10 shows the menu that comes up after we right-click the lu_fact_types table on the left.

Figure 4-10. Import menu in pgAdmin4

Exporting queries as a structured file or report in pgAdmin

In addition to importing data, you can export your queries as well. pgAdmin3 allows exporting to delimited CSV, HTML, or XML formats. The pgAdmin4 export feature is much simpler and basic than pgAdmin3.

In pgAdmin to export with delimiters, perform the following:

Open the query window ( ).

Write the query.

In pgAdmin3, you’d choose File→Export. In pgAdmin4, you click the download icon ( ) and browse to where you want to save.

For pgAdmin3, you get additional prompts before being given a save option. Fill out the settings as shown in Figure 4-11.

Figure 4-11. Export menu

Exporting as HTML or XML is much the same, except you use the File→Quick Report option (see Figure 4-12).

Figure 4-12. Export report options

Backup and Restore

pgAdmin offers a graphical interface to pg_dump and pg_restore , covered in “Backup and Restore”. In this section, we’ll repeat some of the same examples using pgAdmin instead of the command line.

If several versions of PostgreSQL or pgAdmin are installed on your computer, it’s a good idea to make sure that the pgAdmin version is using the versions of the utilities that you expect. Check what the bin setting in pgAdmin is pointing to in order to ensure it’s the latest available, as shown in Figure 4-13.

Figure 4-13. pgAdmin File→Preferences

Warning

If your server is remote or your databases are huge, we recommend using the command-line tools for backup and restore instead of pgAdmin to avoid adding another layer of complexity to what could already be a pretty lengthy process. Also keep in mind that if you do a compressed/TAR/directory backup with a newer version of pg_dump , you need to use the same or later version of pg_restore .

Backing up an entire database

In “Selective Backup Using pg_dump”, we demonstrated how to back up a database. To repeat the same steps using the pgAdmin interface, right-click the database you want to back up and choose Custom for Format, as shown in Figure 4-14.

Figure 4-14. Backup database

Backing up systemwide objects

pgAdmin provides a graphical interface to pg_dumpall for backing up system objects. To use the interface, first connect to the server you want to back up. Then, from the top menu, choose Tools→Backup Globals.

pgAdmin doesn’t give you control over which global objects to back up, as the command-line interface does. pgAdmin backs up all tablespaces and roles.

If you ever want to back up the entire server, invoke pg_dumpall by going to the top menu and choosing Tools→Backup Server.

Selective backup of database assets

pgAdmin provides a graphical interface to pg_dump for selective backup. Right-click the asset you want to back up and select Backup (see Figure 4-15). You can back up an entire database, a particular schema, a table, or anything else.

Figure 4-15. pgAdmin schema backup

To back up the selected asset, you can forgo the other tabs (see Figure 4-14). In pgAdmin3, you can selectively drill down to more items by clicking the Objects tab, as shown in Figure 4-16. This feature is not yet present in pgAdmin4.

Figure 4-16. pgAdmin3 selective backup Objects tab

Behind the scenes, pgAdmin simply runs pg_dump to perform backups. If you ever want to know the actual commands pgAdmin is using, say for scripting, look at the Messages tab after you click the Backup button. You’ll see the exact call with arguments to pg_dump .

pgScript

pgScript is a built-in scripting tool in pgAdmin3 but is not present in pgAdmin4. It’s most useful for running repetitive SQL tasks. pgScript can make better use of memory, and thus be more efficient, than equivalent PostgreSQL functions. This is because stored functions maintain all their work in memory and commit all the results of a function in a single batch. In contrast, pgScript commits each SQL insert or update statement as it runs through the script. This makes pgScript particularly handy for memory-hungry processes that you don’t need completed as single transactions. After each transaction commits, memory becomes available for the next one. You can see an example where we use pgScript for batch geocoding at Using pgScript for Geocoding.

The pgScript language is lazily typed and supports conditionals, loops, data generators, basic print statements, and record variables. The general syntax is similar to that of Transact SQL, the stored procedure language of Microsoft SQL Server. Variables, prepended with @ , can hold scalars or arrays, including the results of SQL commands. Commands such as DECLARE and SET , and control constructs such as IF-ELSE and WHILE loops, are part of the pgScript language.

Launch pgScript by opening a regular SQL query window. After typing in your script, execute it by clicking the pgScript icon ( ).

We’ll now show you some examples of pgScripts. Example 4-1 demonstrates how to use pgScript record variables and loops to build a crosstab table, using the lu_fact_types table we create in Example 7-22. The pgScript creates an empty table called census.hisp_pop with numeric columns: hispanic_or_latino , white_alone , black_or_african_american_alone , and so on.

Example 4-1. Create a table using record variables in pgScript

Although pgScript does not have an execute command that allows you to run dynamically generated SQL, we accomplished the same thing in Example 4-1 by assigning an SQL string to a variable. Example 4-2 pushes the envelope a bit further by populating the census.hisp_pop table we just created.

Example 4-2. Populating tables with pgScript loop

The lesson to take away from Example 4-2 is that you can dynamically append SQL fragments into a variable.

Graphical Explain

One of the great gems in pgAdmin is its at-a-glance graphical explain of the query plan. You can access the graphical explain plan by opening up an SQL query window, writing a query, and clicking the explain icon ( ).

Suppose we run the query:

We will get the graphical explain shown in Figure 4-17. Here’s a quick tip for interpreting the graphical explain: trim the fat! The fatter the arrow, the longer a step takes to complete.

Figure 4-17. Graphical explain example

Graphical explain is disabled if Query→Explain→Buffers is enabled. So make sure to uncheck buffers before trying a graphical explain. In addition to the graphical explain, the Data Output tab shows the textual explain plan, which for this example looks like:

Job Scheduling with pgAgent

pgAgent is a handy utility for scheduling PostgreSQL jobs. But it can also execute batch scripts on the OS, replacing crontab on Linux/Unix and the Task Scheduler on Windows. pgAgent goes even further: you can schedule jobs to run on any other host regardless of OS. All you have to do is install the pgAgent service on the host and point it to use a specific PostgreSQL database with pgAgent tables and functions installed. The PostgreSQL server itself is not required, but the client connection libraries are. Because pgAgent is built atop PostgreSQL, you are blessed with the added advantage of having access to all the tables controlling the agent. If you ever need to replicate a complicated job multiple times, you can go straight into the database tables directly and insert the records for new jobs, skipping the pgAdmin interface.

We’ll get you started with pgAgent in this section. Visit Setting Up pgAgent and Doing Scheduled Backups to see more working examples and details on how to set it up.

Installing pgAgent

You can download pgAgent from pgAgent Download. It is also available via the EDB Application Stackbuilder and BigSQL package. The packaged extension script creates a new schema named pgAgent in the postgres database. When you connect to your server via pgAdmin, you will see a new section called Jobs, as shown in Figure 4-18.

Figure 4-18. pgAdmin4 with pgAgent installed

Although pgAgent is installed by default in postgres db, you can install in a different database using CREATE EXTENSION pgagent; . If you decide to install in a different database, make sure to set your pgagent service to use that database and in pgAdmin set the maintenance db in the server connection tab to be this database.

If you want pgAgent to run batch jobs on additional servers, follow the same steps, except that you don’t have to reinstall the SQL script packaged with pgAgent. Pay particular attention to the OS permission settings of the pgAgent service/daemon account. Make sure each agent has sufficient privileges to execute the batch jobs that you will be scheduling.

Warning

Batch jobs often fail in pgAgent even when they might run fine from the command line. This is often due to permission issues. pgAgent always runs under the same account as the pgAgent service/daemon. If this account doesn’t have sufficient privileges or the necessary network path mappings, jobs fail.

Scheduling Jobs

Each scheduled job has two parts: the execution steps and the schedule. When creating a new job, start by adding one or more job steps. Figure 4-19 shows what the step add/edit screen looks like.

Figure 4-19. pgAdmin4 step edit screen

For each step, you can enter an SQL statement to run, point to a shell script on the OS, or even cut and paste in a full shell script as we commonly do.

If you choose SQL, the connection type option becomes enabled and defaults to local. With a local connection, the job step runs on the same server as the pgAgent and uses the same authentication username and password. You need to additionally specify the database that pgAgent should connect to in order to run the jobs. The screen offers you a drop-down list of databases to choose from. If you choose a remote connection type, the text box for entering a connection string becomes enabled. Type in the full connection string, including credentials and the database. When you connect to a remote PostgreSQL server with an earlier version of PostgreSQL, make sure that all the SQL constructs you use are supported on that version.

If you choose to run batch jobs, the syntax must be specific to the OS running the job. For example, if your pgAgent is running on Windows, your batch jobs should have valid DOS commands. If you are on Linux, your batch jobs should have valid shell or Bash commands.

Steps run in alphabetical order, and you can decide what kinds of actions you want to take upon success or failure of each step. You have the option of disabling steps that should remain dormant but that you don’t want to delete because you might reactivate them later.

Once you have the steps ready, go ahead and set up a schedule to run them. You can set up intricate schedules with the scheduling screen. You can even set up multiple schedules.

If you installed pgAgent on multiple servers and have them all pointing to the same pgAgent database, all these agents by default will execute all jobs.

If you want to run the job on just one specific machine, fill in the host agent field when creating the job. Agents running on other servers will skip the job if it doesn’t match their hostname.

pgAgent consists of two parts: the data defining the jobs and the logging of the job. Log information resides in the pgAgent schema, usually in the postgres database; the job agents query the jobs for the next job to run and then insert relevant logging information in the database. Generally, both the PostgreSQL server holding the data and the job agent executing the jobs reside on the same server, but they are not required to. Additionally, a single PostgreSQL server can service many job agents residing on different servers.

A fully formed job is shown in Figure 4-20.

Figure 4-20. pgAgent jobs in pgAdmin

Helpful pgAgent Queries

With your finely honed SQL skills, you can easily replicate jobs, delete jobs, and edit jobs directly by messing with pgAgent metatables. Just be careful! For example, to get a glimpse inside the tables controlling all of your agents and jobs, connect to the postgres database and execute the query in Example 4-3.

Example 4-3. Description of pgAgent tables

Although pgAdmin already provides an intuitive interface to pgAgent scheduling and logging, you may find the need to generate your own job reports. This is especially true if you have many jobs or you want to compile stats from your job results. Example 4-4 demonstrates the one query we use often.

Example 4-4. List log step results from today

We find this query essential for monitoring batch jobs because sometimes a job will report success even though it failed. pgAgent can’t always discern the success or failure of a shell script on the OS. The jsloutput field in the logs provides the shell output, which usually details what went wrong.

Warning

In some versions of pgAgent running on Windows, shell scripts often default to failed even when they succeeded. If this happens, you should set the step status to ignore . This is a known bug that we hope will be fixed in a future release.

Get PostgreSQL: Up and Running, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Query Tool¶

The Query Tool is a powerful, feature-rich environment that allows you to execute arbitrary SQL commands and review the result set. You can access the Query Tool via the Query Tool menu option on the Tools menu, or through the context menu of select nodes of the Browser tree control. The Query Tool allows you to:

Issue ad-hoc SQL queries.

Execute arbitrary SQL commands.

Edit the result set of a SELECT query if it is updatable .

Displays current connection and transaction status as configured by the user.

Save the data displayed in the output panel to a CSV file.

Review the execution plan of a SQL statement in either a text, a graphical format or a table format (similar to https://explain.depesz.com).

View analytical information about a SQL statement.

You can open multiple copies of the Query tool in individual tabs simultaneously. To close a copy of the Query tool, click the X in the upper-right hand corner of the tab bar.

The Query Tool features two panels:

The upper panel displays the SQL Editor. You can use the panel to enter, edit, or execute a query. It also shows the History tab which can be used to view the queries that have been executed in the session, and a Scratch Pad which can be used to hold text snippets during editing. If the Scratch Pad is closed, it can be re-opened (or additional ones opened) by right-clicking in the SQL Editor and other panels and adding a new panel.

The lower panel displays the Data Output panel. The tabbed panel displays the result set returned by a query, information about a query’s execution plan, server messages related to the query’s execution and any asynchronous notifications received from the server.

Toolbar¶

The toolbar is described in the following subsections.

The SQL Editor Panel¶

The SQL editor panel is a workspace where you can manually provide a query, copy a query from another source, or read a query from a file. The SQL editor features syntax coloring and autocompletion.

To use autocomplete, begin typing your query; when you would like the Query editor to suggest object names or commands that might be next in your query, press the Control+Space key combination. For example, type “SELECT * FROM” (without quotes, but with a trailing space), and then press the Control+Space key combination to select from a popup menu of autocomplete options.

After entering a query, select the Execute/Refresh icon from the toolbar. The complete contents of the SQL editor panel will be sent to the database server for execution. To execute only a section of the code that is displayed in the SQL editor, highlight the text that you want the server to execute, and click the Execute/Refresh icon.

The message returned by the server when a command executes is displayed on the Messages tab. If the command is successful, the Messages tab displays execution details.

Options on the Edit menu offer functionality that helps with code formatting and commenting:

The auto-indent feature will automatically indent text to the same depth as the previous line when you press the Return key.

Block indent text by selecting two or more lines and pressing the Tab key.

Implement or remove SQL style or toggle C style comment notation within your code.

You can also drag and drop certain objects from the treeview which can save time in typing long object names. Text containing the object name will be fully qualified with schema. Double quotes will be added if required. For functions and procedures, the function name along with parameter names will be pasted in the Query Tool.

Query History Panel¶

Use the Query History tab to review activity for the current session:

The Query History tab displays information about recent commands:

The date and time that a query was invoked.

The text of the query.

The number of rows returned by the query.

The amount of time it took the server to process the query and return a result set.

Messages returned by the server (not noted on the Messages tab).

The source of the query (indicated by icons corresponding to the toolbar).

You can show or hide the queries generated internally by pgAdmin (during ‘View/Edit Data’ or ‘Save Data’ operations).

You can remove a single query by selecting it and clicking on the Remove button. If you would like to remove all of the histories from the Query History tab, then click on the Remove All button.

By using the Copy button, you can copy a particular query to the clipboard, and with the Copy to Query Editor button, you can copy a specific query to the Query Editor tab. During this operation, all existing content in the Query Editor is erased.

Query History is maintained across sessions for each database on a per-user basis when running in Query Tool mode. In View/Edit Data mode, history is not retained. By default, the last 20 queries are stored for each database. This can be adjusted in config_local.py or config_system.py (see the config.py documentation) by overriding the MAX_QUERY_HIST_STORED value. See the Deployment section for more information.

The Data Output Panel¶

The Data Output panel displays data and statistics generated by the most recently executed query.

The Data Output tab displays the result set of the query in a table format. You can:

Select and copy from the displayed result set.

Use the Execute/Refresh options to retrieve query execution information and set query execution options.

Use the Save results to file icon to save the content of the Data Output tab as a comma-delimited file.

Edit the data in the result set of a SELECT query if it is updatable.

A result set is updatable if:

All columns are either selected directly from a single table, or are not table columns at all (e.g. concatenation of 2 columns). Only columns that are selected directly from the table are editable, other columns are read-only.

All the primary key columns or OIDs of the table are selected in the result set.

Any columns that are renamed or selected more than once are also read-only.

Editable and read-only columns are identified using pencil and lock icons (respectively) in the column headers.

The psycopg2 driver version should be equal to or above 2.8 for updatable query result sets to work.

An updatable result set is identical to the Data Grid in View/Edit Data mode, and can be modified in the same way.

If Auto-commit is off, the data changes are made as part of the ongoing transaction, if no transaction is ongoing a new one is initiated. The data changes are not committed to the database unless the transaction is committed.

If any errors occur during saving (for example, trying to save NULL into a column with NOT NULL constraint) the data changes are rolled back to an automatically created SAVEPOINT to ensure any previously executed queries in the ongoing transaction are not rolled back.

All rowsets from previous queries or commands that are displayed in the Data Output panel will be discarded when you invoke another query; open another Query Tool tab to keep your previous results available.

Explain Panel¶

To generate the Explain or Explain Analyze plan of a query, click on Explain or Explain Analyze button in the toolbar.

More options related to Explain and Explain Analyze can be selected from the drop down on the right side of Explain Analyze button in the toolbar.

Please note that pgAdmin generates the Explain [Analyze] plan in JSON format.

On successful generation of Explain plan, it will create three tabs/panels under the Explain panel.

Please note that EXPLAIN VERBOSE cannot be displayed graphically. Click on a node icon on the Graphical tab to review information about that item; a popup window will display on the right side with the information about the selected object. For information on JIT statistics, triggers and a summary, click on the button on top-right corner; a similar popup window will be displayed when appropriate.

Use the download button on top left corner of the Explain canvas to download the plan as an SVG file.

Note: Download as SVG is not supported on Internet Explorer.

Note that the query plan that accompanies the Explain analyze is available on the Data Output tab.

Table tab shows the plan details in table format, it generates table format similar to explain.depesz.com. Each row of the table represent the data for a Explain Plan Node. It may contain the node information, exclusive timing, inclusive timing, actual vs planned rows differences, actual rows, planned rows, loops.

background color of the exclusive, inclusive, and Rows X columns may vary based on the difference between actual vs planned.

If percentage of the exclusive/inclusive timings of the total query time is: > 90 — Red color > 50 — Orange (between red and yellow) color > 10 — Yellow color

If planner mis-estimated number of rows (actual vs planned) by 10 times — Yellow color 100 times — Orange (between Red and Yellow) color 1000 times — Red color

Statistics tab shows two tables: 1. Statistics per Plan Node Type 2. Statistics per Table

Messages Panel¶

Use the Messages tab to view information about the most recently executed query:

If the server returns an error, the error message will be displayed on the Messages tab, and the syntax that caused the error will be underlined in the SQL editor. If a query succeeds, the Messages tab displays how long the query took to complete and how many rows were retrieved:

Notifications Panel¶

Use the Notifications tab to view the notifications using PostgreSQL Listen/ Notify feature. For more details see PostgreSQL documentation.

Execute LISTEN “foo” in first Query Tool session

2. In the another Query Tool session, execute Notify command or pg_notify function to send the notification of the event together with the payload.

3. You can observe the Notification tab in the first Query Tool session where it shows the Recorded time, Event, Process ID, and the Payload of the particular channel.

Graph Visualiser Panel¶

Click the Graph Visualiser button in the toolbar to generate the Graphs of the query results. The graph visualiser supports Line Charts, Stacked Line Charts, Bar Charts, Stacked Bar Charts, and Pie Charts.

Choose the type of the graph that you would like to generate.

Choose the column whose value you wish to display on X-axis from the X Axis dropdown. Select the <Row Number> option to use the number of rows as labels on the X-axis.

Choose the columns whose value you wish to display on Y-axis from the Y Axis dropdown. Users can choose multiple columns. Choose the <Select All> option from the drop-down menu to select all the columns.

Download and Zoom button

Zooming is performed by clicking and selecting an area over the chart with the mouse. The Zoom to original button will bring you back to the original zoom level.

Click the Download button on the button bar to download the chart.

Line Chart¶

The Line Chart can be generated by selecting the ‘Line Chart’ from the Graph Type drop-down, selecting the X-axis and the Y-axis, and clicking on the ‘Generate’ button. Below is an example of a chart of employee names and their salaries.

Set Use different data point styles? option to true in the Preferences Dialog , to show data points in a different style on each graph lines.

Stacked Line Chart¶

The Stacked Line Chart can be generated by selecting the ‘Stacked Line Chart’ from the Graph Type drop-down, selecting the X-axis and the Y-axis, and clicking on the ‘Generate’ button.

Bar Chart¶

The Bar Chart can be generated by selecting the ‘Bar Chart’ from the Graph Type drop-down, selecting the X-axis and the Y-axis, and clicking on the ‘Generate’ button.

Stacked Bar Chart¶

The Stacked Bar Chart can be generated by selecting the ‘Stacked Bar Chart’ from the Graph Type drop-down, selecting the X-axis and the Y-axis, and clicking on the ‘Generate’ button.

Pie Chart¶

The Pie Chart can be generated by selecting the ‘Pie Chart’ from the Graph Type drop-down, selecting the Label and Value, and clicking on the ‘Generate’ button.

Connection Status¶

Use the Connection status feature to view the current connection and transaction status by clicking on the status icon in the Query Tool:

Change connection¶

User can connect to another server or database from existing open session of query tool.

Click on the connection link next to connection status.

Now click on the <New Connection> option from the dropdown.

Now select server, database, user, and role to connect and click on the ‘Save’ button.

A newly created connection will now get listed in the options.

To connect, select the newly created connection from the dropdown list.

Macros¶

Query Tool Macros enable you to execute pre-defined SQL queries with a single key press. Pre-defined queries can contain the placeholder $SELECTION$. Upon macro execution, the placeholder will be replaced with the currently selected text in the Query Editor pane of the Query Tool.

To create a macro, select the Manage Macros option from the Macros menu on the Query Tool. Select the key you wish to use, enter the name of the macro, and the query, optionally including the selection placeholder, and then click the Save button to store the macro.

To delete a macro, select the macro on the Manage Macros dialogue, and then click the Delete button. The server will prompt you for confirmation to delete the macro.

To execute a macro, simply select the appropriate shortcut keys, or select it from the Macros menu.

Установка PostgreSQL 11 на Windows. Пошаговая инструкция

PostgreSQL 12 и pgAdmin 4 мы установили, теперь давайте запустим pgAdmin 4, подключимся к серверу и настроим рабочую среду pgAdmin.

Чтобы запустить pgAdmin 4, зайдите в меню пуск, найдите пункт PostgreSQL 12, а в нем pgAdmin 4.

Подключение к серверу PostgreSQL 12

pgAdmin 4 имеет веб интерфейс, поэтому в результате у Вас должен запуститься браузер, а в нем открыться приложение pgAdmin 4.

При первом запуске pgAdmin 4 появится окно «Set Master Password», в котором мы должны задать «мастер-пароль», это можно и не делать, однако если мы будем сохранять пароль пользователя (галочка «Сохранить пароль»), например, для того чтобы каждый раз при подключении не вводить его, то настоятельно рекомендуется придумать и указать здесь дополнительный пароль, это делается один раз.

Вводим и нажимаем «ОК».

Чтобы подключиться к только что установленному локальному серверу PostgreSQL в обозревателе серверов, щелкаем по пункту «PostgreSQL 12».

В итоге запустится окно «Connect to Server», в котором Вам нужно ввести пароль системного пользователя postgres, т.е. это тот пароль, который Вы придумали, когда устанавливали PostgreSQL. Вводим пароль, ставим галочку «Save Password», для того чтобы сохранить пароль и каждый раз не вводить его (благодаря функционалу «мастер-пароля», все сохраненные таким образом пароли будут дополнительно шифроваться).

В результате Вы подключитесь к локальному серверу PostgreSQL 12 и увидите все объекты, которые расположены на данном сервере.

Заметка! Как создать базу данных в PostgreSQL с помощью pgAdmin 4.

Установка русского языка в pgAdmin 4

Как видите, по умолчанию интерфейс pgAdmin 4 на английском языке, если Вас это не устраивает, Вы можете очень просто изменить язык на тот, который Вам нужен. pgAdmin 4 поддерживает много языков, в том числе и русский.

Для того чтобы изменить язык pgAdmin 4, необходимо зайти в меню «File -> Preferences».

Затем найти пункт «User Languages», и в соответствующем поле выбрать значение «Russian». Для сохранения настроек нажимаем «Save», после этого перезапускаем pgAdmin 4 или просто обновляем страницу в браузере.

В результате pgAdmin 4 будет русифицирован.

Заметка! Как создать составной тип данных в PostgreSQL.

Пример написания SQL запроса в Query Tool (Запросник)

Для того чтобы убедиться в том, что наш сервер PostgreSQL работает, давайте напишем простой запрос SELECT, который покажет нам версию сервера PostgreSQL.

Для написания SQL запросов в pgAdmin 4 используется инструмент Query Tool или на русском «Запросник», его можно запустить с помощью иконки на панели или из меню «Инструменты».

После того как Вы откроете Query Tool, напишите

Этот запрос показывает версию PostgreSQL.

Как видите, все работает!

Получение справки

Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через \h.

\h без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис SELECT или ALTER TABLE. Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. \h alter поможет в этом и предоставит более чем подробную информацию. psql использует пагинацию для большого вывода, так что там работает поиск. Наберите / + текст для поиска + и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно. Поискав NULL, находится вот такая команда: ALTER [ COLUMN ] column_name < SET | DROP >NOT NULL. Теперь можно составить нужную команду, не заглядывая в Google.

ALTER TABLE table_name ALTER column_name DROP NOT NULL;

Как видите, \h даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть \?.

Сравнение вывода

Когда вы запускаете команду с помощью psql, результат оказывается в том же терминале. Команда \o позволяет указать имя файла, куда будет записан вывод. \o output.txt перенаправит вывод, а \o вернёт всё обратно.

Ну и зачем это надо? Некоторые используют для экспорта данных (позже покажу способ получше). Я же использую \o для сравнение чего-либо тяжёлого, например, планов выполнения с помощью \! vimdiff.

db=# \o a.txt db=# EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM groups WHERE name = ‘admins’); db=# \o b.txt db=# EXPLAIN SELECT users.* FROM users LEFT JOIN groups WHERE groups.name = ‘admins’; db=# \! vimdiff a.txt b.txt

Теперь я могу просмотреть оба плана выполнения одновременно и легко выявить различия.

Построение запросов

psql превосходно подходит для построения небольших запросов, однако для многострочных и вложенных надо использовать более адекватный инструмент. Например, \e — она открывает последний запрос в вашем любимом редакторе, который может уже обеспечить и подсветку синтаксиса, и автодополнение, и прочие полезные вещи. Для выполнения достаточно сохранить этот псевдо-файл и выйти (ну прям как с git). \e удобно использовать для итеративного построения большого запроса.