How To Create Table In Postgresql Using Pgadmin 4

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.

Note

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.
SQL pane: 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.
Grant wizard: This timesaver allows you to change privileges on many database objects in one fell swoop.
pgScript engine: 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.
pgAgent: 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.

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.

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.

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.

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.

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".

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.

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.

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.

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.
Run 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.

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

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.

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.

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.

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.

Tip

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

DECLARE @I, @labels, @tdef; SET @I = 0;              Labels will hold records.              SET @labels =     SELECT         quote_ident(             replace(                 replace(lower(COALESCE(fact_subcats[4], fact_subcats[3])), ' ', '_') ,':',''             )         ) As col_name,     fact_type_id     FROM census.lu_fact_types     WHERE category = 'Population' AND fact_subcats[3] ILIKE 'Hispanic or Latino%'     ORDER BY short_name;  SET @tdef = 'census.hisp_pop(tract_id varchar(11) PRIMARY KEY ';              Loop through records using LINES function.              WHILE @I < LINES(@labels) BEGIN     SET @tdef = @tdef + ', ' + @labels[@I][0] + ' numeric(12,3) ';     SET @I = @I + 1; END  SET @tdef = @tdef + ')';              Print out table def.              PRINT @tdef;              create the table.              CREATE TABLE @tdef;

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

              DECLARE              @              I              ,              @              labels              ,              @              tload              ,              @              tcols              ,              @              fact_types              ;              SET              @              I              =              0              ;              SET              @              labels              =              SELECT              quote_ident              (              replace              (              replace              (              lower              (              COALESCE              (              fact_subcats              [              4              ],              fact_subcats              [              3              ])),              ' '              ,              '_'              ),              ':'              ,              ''              )              )              As              col_name              ,              fact_type_id              FROM              census              .              lu_fact_types              WHERE              category              =              'Population'              AND              fact_subcats              [              3              ]              ILIKE              'Hispanic or Latino%'              ORDER              BY              short_name              ;              SET              @              tload              =              'tract_id'              ;              SET              @              tcols              =              'tract_id'              ;              SET              @              fact_types              =              '-1'              ;              WHILE              @              I              <              LINES              (              @              labels              )              BEGIN              SET              @              tcols              =              @              tcols              +              ', '              +              @              labels              [              @              I              ][              0              ]              ;              SET              @              tload              =              @              tload              +              ', MAX(CASE WHEN fact_type_id = '              +              CAST              (              @              labels              [              @              I              ][              1              ]              AS              STRING              )              +              ' THEN val ELSE NULL END)'              ;              SET              @              fact_types              =              @              fact_types              +              ', '              +              CAST              (              @              labels              [              @              I              ][              1              ]              As              STRING              );              SET              @              I              =              @              I              +              1              ;              END              INSERT              INTO              census              .              hisp_pop              (              @              tcols              )              SELECT              @              tload              FROM              census              .              facts              WHERE              fact_type_id              IN              (              @              fact_types              )              AND              yr              =              2010              GROUP              BY              tract_id              ;

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:

            SELECT            left            (            tract_id            ,            5            )            As            county_code            ,            SUM            (            hispanic_or_latino            )            As            tot            ,            SUM            (            white_alone            )            As            tot_white            ,            SUM            (            COALESCE            (            hispanic_or_latino            ,            0            )            -            COALESCE            (            white_alone            ,            0            ))            AS            non_white            FROM            census            .            hisp_pop            GROUP            BY            county_code            ORDER            BY            county_code            ;

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.

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:

GroupAggregate  (cost=111.29..151.93 rows=1478 width=20)   Output: ("left"((tract_id)::text, 5)), sum(hispanic_or_latino),  sum(white_alone), ...   ->  Sort  (cost=111.29..114.98 rows=1478 width=20)      Output: tract_id, hispanic_or_latino, white_alone,      ("left"((tract_id)::text, 5)) 	Sort Key: ("left"((tract_id)::text, 5)) 	->  Seq Scan on census.hisp_pop  (cost=0.00..33.48 rows=1478 width=20) 		  Output: tract_id, hispanic_or_latino 		   , white_alone, "left"((tract_id)::text, 5)

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.

Note

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.

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.

Tip

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.

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

                SELECT                c                .                relname                As                table_name                ,                d                .                description                FROM                pg_class                As                c                INNER                JOIN                pg_namespace                n                ON                n                .                oid                =                c                .                relnamespace                INNER                JOIN                pg_description                As                d                ON                d                .                objoid                =                c                .                oid                AND                d                .                objsubid                =                0                WHERE                n                .                nspname                =                'pgagent'                ORDER                BY                c                .                relname                ;

table_name     |       description ---------------+------------------------- pga_job        | Job main entry pga_jobagent   | Active job agents pga_jobclass   | Job classification pga_joblog     | Job run logs. pga_jobstep    | Job step to be executed pga_jobsteplog | Job step run logs. pga_schedule   | Job schedule exceptions

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

                SELECT                j                .                jobname                ,                s                .                jstname                ,                l                .                jslstart                ,                l                .                jslduration                ,                l                .                jsloutput                FROM                pgagent                .                pga_jobsteplog                As                l                INNER                JOIN                pgagent                .                pga_jobstep                As                s                ON                s                .                jstid                =                l                .                jsljstid                INNER                JOIN                pgagent                .                pga_job                As                j                ON                j                .                jobid                =                s                .                jstjobid                WHERE                jslstart                >                CURRENT_DATE                ORDER                BY                j                .                jobname                ,                s                .                jstname                ,                l                .                jslstart                DESC                ;

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.