Query Tool vs. View/Edit Data in pgAdmin 4

May 10, 2022

Overview

pgAdmin 4 provides 2 different ways to execute queries on the database:

  • Query tool: 

You can write and execute custom queries on any table in the connected database.

  • View/Edit data: 

View/Edit data can be used to view or modify a particular table's data.

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 browser tree tools menu or through the context menu of selected nodes of the browser tree control. You can open multiple copies of the Query tool in individual tabs simultaneously.

Query Tool

Query Editor

The Query 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 Query editor features syntax coloring and autocompletion.

Let's try some examples for the Query Tool:

  • Create Table:

Create Table

Create Table

You can copy-paste the query in the query tool to create a new table. Similarly, you can create new database and database objects like Schemas, Functions, Materialized views, etc, using the query tool

  • Alter Table:

Alter the demo table, update the column name to first_name and add a new column last_name.

Alter Table

Alter Table

  • Insert and Delete records:

Insert and Delete records from the same demo Table.

Demo Table

Demo table

public demo

Public demo

Fetch records

Fetch records

These are some basic queries you can execute in the query tool. You can run any custom query using the Query Tool.

Query History

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 by toggling the Show queries generated internally by pgAdmin? option. To erase the content of the Query History tab, select Remove or Remove All from the top of the query history panel.

History Panel

Scratch Pad

This is used to hold text snippets during editing. You can close the scratch Pad by clicking the close button.

Toolbar:

The Query Tool toolbar uses context-sensitive icons that provide shortcuts to frequently performed tasks. If an icon is highlighted, the option is enabled; if the icon is grayed out, the task is disabled. 

Tools available in Query tool:

  • Open File
  • Save File (You can select the Save As option from the dropdown)
  • Edit: You will get different settings related to the Query Editor, e.g: Find, Replace, Format SQL, etc.
  • Cancel Query
  • Execute/Refresh: To execute the query which is written in the Query Editor.
  • Explain and Explain Analyze
  • Commit/Rollback: If Auto Commit is disabled, you can manually execute the Commit or Rollback by using these buttons
  • Macros: This option enables you to execute predefined SQL queries with a single keypress. Use the Manage macros form drop-down to manage all macros.

Data output Panel

The Data Output panel displays data and statistics generated by the most recently executed query. It shows the result in table format and allows editing of the records in the table.

Note: To modify the content of a table, each row in the table must be uniquely identifiable. If the table definition does not include an OID or a primary key, the displayed data is read-only.

It also provides the following operations on the Data output table:

  • Add row: Adds the new row in the table, you can enter the values in the row and save them in the table.
  • Copy: Allows you to copy the selected Row(s) or cell(s), you can also copy the whole table with table headers just need to select the Copy with headers option from the dropdown which is present next to the Copy button. To select the whole table click on the top-left most cell in the table, you can also select the specific column by clicking on the column header.
  • Paste: Allows you to paste the copied rows, It will add a new row on paste.
  • Delete: Allows you to delete the selected row(s). It will mark the rows as deleted on the UI table, to save the changes on the server you need to click on the Save Data Changes button.
  • Save Data Changes: Allows you to save the changes which you have made in the Data output panel.
  • Save results to file: Aallows you to save the data displayed in the output panel to a CSV file and download it.

Explain Plan

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.

Messages Panel

This panel shows the information about the most recently executed query. If the server returns an error, the error message will be displayed on the Messages tab.

Notifications Panel

Use the Notifications tab to view the notifications using the PostgreSQL Listen/Notify feature.

View/Edit Data 

The View/Edit Data panel uses the same user interface as the Query Tool. It provides a quick way to view data from a table without writing queries. However, queries created view/edit can not be manually edited.

Options available in the View/Edit Data context menu:

  • All Rows
  • First 100 Rows: Fetches the first 100 rows.
  • Last 100 Rows: Fetches the last 100 rows.
  • Filtered Rows: You can specify any custom filter condition for fetching the data.

View / edit data

View / edit data

Although similar to the Query Tool in design, you can perform more filtering on table data using View/Edit Data.

Query Editor

In the View/Edit Data panel a query is generated by pgAdmin based on the inputs the user provided while opening the View/Edit Data panel for the table. You can’t edit the query in the Query Editor

Example:

  • Filter rows and change record order:

Click on the Sort/Filter button on the toolbar, It will open a dialog where you can add filter conditions and sort on the column(s).

sort / filter

Once you click on the Save button, pgAdmin will update the query in Query Editor as per the filter and data sorting order selected by you.

sort / filter

Here you can check that pgAdmin added the WHERE condition and updated the sorting order to ASC  as per the user selections in the Sort/Filter dialog.

Query History

It provides the same functionality present in Query Tool, as discussed in the Query Tool section.

Toolbar

This toolbar is the same toolbar present in the Query Tool but there are a few features you can only use in the View/Edit Data.

Tools available in View/Edit Data:

  • Sort/ Filter: You can add sorting or filtering to the query.

sort / filter options

  • Limit: You can set the limit to fetch the limited result for the query. No Limit option will fetch all the records.
  • Cancel Query
  • Execute/Refresh: To execute the query which is written by the Query Editor.
    Note: Dropdown for Auto Commit and Auto Rollback is disabled for View/Edit Data.

Note: All other options in the toolbar will be disabled so the user can not use them in View/Edit Data.

View/Edit Data provides the same Data output, Messages, and Notifications panels with the same functionality we discussed earlier in the Query Tool section.

Conclusion

pgAdmin 4 provides two different ways to execute queries. 

The Query Tool is mainly focused on giving the user the ability to write and execute custom queries. If you want to execute different queries like SELECT, INSERT, DELETE, etc you should use the Query Tool. 

View/Edit Data is more focused on the single table interaction and performs the operations on that one table's data. If you want to fetch, update, or delete data from a particular table go with View/Edit Data.

Ref links:

https://www.pgadmin.org/docs/pgadmin4/latest/query_tool.html

https://www.pgadmin.org/docs/pgadmin4/latest/editgrid.html

Share this

Relevant Blogs

Finding memory leaks in Postgres C code

I spent the last week looking for a memory leak in Postgres’s WAL Sender process. I spent a few days getting more acquainted with Valgrind and gcc/clang sanitizers, but ultimately...
March 27, 2024

Exploring EDB SPL Check: A New Feature in EPAS 16

EDB Postgres Advanced Server (EPAS) has introduced a highly anticipated feature called EDB SPL Check in its 16th version. This extension is a boon for developers working with Stored Procedure...
December 18, 2023

More Blogs

AI, Postgres and You: A Vision for the Future

Let's talk about something I'm very excited about: tech. We’ve all seen tech inventions rise and fall fast in IT, and it's difficult to predict which inventions will make it...
November 14, 2023