Cheatsheet in PostgreSQL
There is no mention of the term “Cheatsheet” But there is a lot of information in the documents that may be combined into a cheatsheet-style overview of the main ideas, instructions, for PostgreSQL and the database management program DBeaver. Based on the supplied, this response will create a summary that can be used as a quick reference guide.
A robust, and extremely extendable object-relational database management system (DBMS) is PostgreSQL, commonly referred to as Postgres. Both online transactional processing (OLTP) and online analytical processing (OLAP) applications can benefit from its many capabilities and ACID compliance.
Core Concepts & Hierarchy
Database Cluster: A database cluster is a group of databases that are managed by a PostgreSQL server installation. The cluster as a whole shares items like roles and tablespaces.
Database: A named collection of SQL objects with one or more schemas is called a database. A template database, usually template1, is used as a model for new databases.
Schema: A database’s namespace that houses items like tables, views, and functions is called a schema. It avoids naming conflicts and aids in object organization. The public schema creates objects by default.
Tables, Rows, and Columns: In a relational model, data is kept in rows (tuples) and columns (attributes) in a table. Each column holds specific info.
Key Command-Line Tools
PostgreSQL includes administrative and data management command-line utilities.
psql: PostgreSQL’s standard distribution includes strong command-line tools for database administration and management, including psql, the main interactive terminal client. Psql, included in all PostgreSQL deployments, is a versatile frontend program that lets users enter, edit, and run SQL queries, see results, and automate activities with shell scripting.
pg_dump: A single PostgreSQL database’s logical backup is made by the pg_dump function and saved as an archive file or script.
pg_dumpall: A complete database cluster, including all databases, roles, and tablespace configurations, can be backed up using pg_dumpall.
pg_restore: Among the primary command-line tools for PostgreSQL administration, pg_restore may restore a database from a non-plain-text archive prepared by pg_dump. Pg_restore is for binary, tar, custom, and directory archive formats, while psql restores plain-text SQL backups.
createdb / dropdb: The standard PostgreSQL distribution includes database administration command-line tools createdb and dropdb. These utilities wrap CREATE DATABASE and DROP DATABASE SQL statements into convenient frontend programs or shell scripts.
createuser / dropuser: The standard PostgreSQL package includes createuser and dropuser, which wrap database role management in the operating system’s command prompt. These tools use SQL commands CREATE ROLE and DROP ROLE.
Common psql Meta-Commands
These commands start with a backslash and are run inside the psql interactive terminal.
\?: The psql interactive terminal, a PostgreSQL command-line client, provides meta-commands or “backslash commands” for database maintenance and other tasks. Use the \? meta-command to get help with client-side commands in psql. In an interactive psql session, typing \? and pressing enter displays a complete list of backslash commands and their functions.
\h [NAME]: PostgreSQL’s main command-line client, psql, has many meta-commands for database administration and information retrieval. One of the most useful tools is \h, which provides inline help for SQL command syntax.
\l: Meta-commands with a backslash enable several administrative actions and shell-like functionality in the psql interactive terminal. \l, a popular meta-command, lists all PostgreSQL databases in the cluster.
\c [DBNAME]: The psql interactive terminal in PostgreSQL processes “backslash” or meta-commands for database administration rather than sending them to the server. A key command for PostgreSQL is \c or \connect, which creates a new connection and closes the old one.
\d [NAME]: Psql’s meta-commands, centered on the backslash-d (\d) command, are widely used and powerful. The basic form is \d You want to investigate a database object named [NAME]. This works on any PostgreSQL “relation,” including tables, views, sequences, and indexes. Object definitions, like table columns with data types and modifiers, are output.
\dt, \di, \dv, \df: A collection of meta-commands in the PostgreSQL interactive terminal, psql, can quickly list different database objects without running a full SELECT query against the system catalogs. The \dt, \di, \dv, and \df commands are specialized versions of the \d command, which lists relations like tables, views, and indexes. These shortcuts filter by object type for a more focused output. Display a list of tables with \dt. \di lists indexes. Use the \dv command to list views.
\x: The PostgreSQL interactive terminal psql regularly uses the \x meta-command to toggle enlarged query result display. This command is one of the “shell-like features” psql offers for various operations. In contrast to the aligned table output, the expanded format lists each record’s columns vertically, one per line, followed by its value.
\timing: Timing is a handy meta-command in PostgreSQL’s psql interactive terminal for measuring performance. This toggle command tells psql to display the execution time for each SQL statement. To enable or disable this feature, type \timing or use \timing on/off.
\copy: The \copy meta-command is a crucial tool for mass data import and export in the psql interactive terminal. As a frontend or client-side copy, the psql program reads or writes a file on the client system and transports data to or from PostgreSQL. Copy is useful for users who need to transfer data from or to their local file system, connected to a distant database server, or lack file system access on the server machine.
Fundamental SQL Commands (DML)
SQL’s Data Manipulation Language (DML) retrieves, inserts, modifies, and deletes data from database tables in PostgreSQL. DML differs from DDL, which defines data structure, and DCL, which maintains access permissions. There are four main DML statements for data modification.
- The most common database retrieval command is SELECT. It lets users choose which columns to return, which tables to query from in the FROM clause, and how to filter results using a WHERE clause to choose rows that fit a criterion. It may be adjusted with clauses like GROUP BY to aggregate data, HAVING to filter results, and ORDER BY to sort output.
- The INSERT statement adds data rows to a table. Provide multiple values to input data one row at a time or in batches. Also, PostgreSQL can insert data from another SELECT query.
- Table data is updated using the UPDATE statement. SET clauses assign new values to columns for all rows that meet the WHERE clause. Without the WHERE clause, all table rows are updated. PostgreSQL’s enhanced UPDATE… FROM syntax lets joins choose which rows to update.
- Finally, the DELETE command deletes table rows. Like UPDATE, a WHERE clause specifies specific rows to delete; without it, all rows are erased. The non-standard PostgreSQL DELETE… USING syntax deletes rows depending on a join with other tables.
Fundamental SQL Commands (DDL)
PostgreSQL uses DDL commands to define, edit, and remove relational database object structures. DDL manages the database schema, unlike DML, which manages data. The main DDL commands are CREATE, ALTER, and DROP. The most popular DDL command is CREATE TABLE, which defines a new table, its columns, data types, and data integrity constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK.
PostgreSQL supports CREATE statements for views, indexes, schemas, user-defined data types, and sequences in addition to tables. ALTER TABLE can add or subtract columns, add or remove constraints, and change a column’s data type. Finally, the DROP command is used to delete a table or view and its data from the database. These PostgreSQL DDL instructions are transactional, rollbackable, and automatically lock database objects to prevent conflicts.
DBeaver GUI Tool Cheatsheet
DBeaver, universal database tool, has a comprehensive GUI for handling several databases, including PostgreSQL. The PostgreSQL standard installation contains command-line tools like psql, while DBeaver provides a graphical alternative for SQL writing, database administration, and design. The Database Navigator panel lets users build and manage connections and examine hierarchical database objects including schemas, tables, views, and functions in DBeaver.
Key UI Components & Actions
Database Navigator: Database Navigator is the main window for managing database connections, schemas, tables, and more. Show all databases in connection options displays all databases for PostgreSQL connections.
SQL Editor: The SQL Editor is essential for developing, running, and managing SQL scripts. It supports SQL formatting, auto-completion, and syntax highlighting.
Data Editor: DBeaver’s Data Editor is a robust UI component for viewing, modifying, and managing database table or view data. The “Data” tab displays when you access a table or view in the Database Navigator or the “Results” page after executing a custom SQL query in the SQL Editor.
Projects: In PostgreSQL, “Projects” is a high-level organizational functionality given by client-side GUI tools like DBeaver. A DBeaver Project lets you aggregate database connections, scripts, and diagrams logically.
Code Example:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
role VARCHAR(50)
);
INSERT INTO employees (name, role)
VALUES ('Alice', 'Developer'),
('Bob', 'Analyst');
SELECT * FROM employees;Output:
CREATE TABLE
INSERT 0 2
id | name | role
----+-------+-----------
1 | Alice | Developer
2 | Bob | Analyst
(2 rows)Essential Shortcuts & Operations
Create a New Connection: New Connection Wizard leads you through DBeaver’s basic database connection process. This wizard can be launched multiple ways for operational flexibility and easy access. The New Connection Wizard button in the main application Toolbar and Database Navigator view is the easiest way.
Execute a Query: Whether using DBeaver, pgAdmin, or psql, PostgreSQL query execution requires different shortcuts and operations. Ctrl+Enter is the main shortcut to execute the SQL statement under the cursor in DBeaver GUI. Other execution choices are available via shortcuts, the main toolbar, or the context menu in DBeaver.
Open Object Editor: In DBeaver, the Object Editor (Metadata editor) is a basic and versatile tool for viewing and interacting with database objects like tables, views, and schemas. Several key shortcuts and procedures can access this editor from the Database Navigator or Projects view.
Transaction Management: Transaction management in DBeaver is governed by the main toolbar, which offers several commit mechanisms for different workflows. DBeaver has two main modes, Auto-Commit and Manual Commit, and the default mode can be adjusted for production or development connections. All changes are quickly transferred to the database in Auto-Commit mode, the default for Development and Test connection types. The auto-commit icon in the toolbar and disabled Commit and Rollback buttons indicate this mode.
Data Import/Export: DBeaver abstracts database commands and simplifies data import and export via a wizard-driven interface. These crucial DBeaver UI operations are usually started via the context menu. Right-click on a table in the Database Navigator or a query’s result set in the SQL Editor and select Export Data.
View Execution Plan: Performance tweaking and understanding how PostgreSQL retrieves data require viewing a query’s execution plan. These plans can be inspected using psql and DBeaver. In PostgreSQL, the EXPLAIN command follows a SQL statement and displays the query planner’s execution plan.
The plan is a tree of nodes with bottom-level scan methods like sequential or index scans and upper-level operations like joins, sorting, and aggregations. The DBeaver GUI displays the execution plan for SQL Editor queries. The key shortcut to plan the query under the cursor is Ctrl+Shift+E.