Data Manipulation Language (DML)
In order to manage and work with data contained in a database, Structured Query Language (SQL) requires Data Manipulation Language (DML). DML is concerned with retrieving, inserting, updating, and removing the actual data within the database structure, as opposed to Data Definition Language (DDL), which specifies and changes the database structure. The usage of DML commands is far higher than that of DDL instructions in a typical database application.
SQL is a declarative programming language, so users can declare what data to get instead of how the database should. PostgreSQL is a declarative object-relational database management system. Its query optimiser determines the most effective technique for processing DML statements. DML’s four fundamental statements are:
SELECT
Retrieve data from one or more tables with this statement. Unless a “volatile” function is employed, SELECT queries seldom affect database data.
Features:
FROM clause: One essential part of a SQL query in PostgreSQL is the FROM clause, which indicates of the query’s rows. It functions as a table expression that can be followed by WHERE, GROUP BY, and HAVING clauses to carry out consecutive transformations by computing a table from one or more other tables.
WHERE clause:In PostgreSQL, the WHERE clause is a crucial component of a SQL SELECT query that filters rows according to a given criterion. It functions as a filter on the intermediate table that the FROM clause derives, appearing after the FROM clause and before the GROUP BY and HAVING clauses.
GROUP BY and HAVING clauses: Group rows by shared values with GROUP BY and HAVING. For advanced aggregations, PostgreSQL supports ROLLUP, CUBE, and GROUPING SETS.
ORDER BY clause:PostgreSQL SELECT statements must include an ORDER BY clause to sort query results in a predetermined order. A query’s rows are delivered in an indeterminate order dependent on physical storage, join algorithms, or other internal considerations without an ORDER BY clause, therefore it’s essential for predictable and consistent results.
LIMIT and OFFSET clauses: The LIMIT and OFFSET clauses in PostgreSQL SELECT statements retrieve only a part of the results generated by the query. The LIMIT and OFFSET clauses set the maximum number of rows to return and the number of rows to skip before returning results.
DISTINCT keyword: PostgreSQL’s DISTINCT keyword in the SELECT statement removes duplicate results to return only unique column combinations. SELECT statements include all rows, even duplicates, by default. The ALL keyword can specify this behaviour.
Subqueries:A powerful component of PostgreSQL is subqueries, which embed SELECT statements in other SQL queries. They can be used in the FROM clause (for the primary query), WHERE clause (for filtering conditions), SELECT list (for calculated attributes), and PostgreSQL LIMIT clause.
Set operations (UNION, EXCEPT, INTERSECT): PostgreSQL set methods like UNION, EXCEPT, and INTERSECT combine many SELECT results into one. Set operations combine data vertically by layering query results, unlike JOIN operations, which combine data horizontally by arranging columns from separate tables side-by-side.
WITH queries (Common Table Expressions – CTEs): Access named, temporary result sets for SELECT, INSERT, UPDATE, and DELETE. The can write and query recursively.
INSERT
Adding new rows of data to a table or view is done with this command.
Features:
VALUES clause: Instead than creating and populating a physical table on disc, PostgreSQL’s VALUES clause offers a straightforward way to establish a “constant table” or “virtual table” within a query.
Inserting multiple rows: PostgreSQL has various efficient ways to insert several rows into a table in one SQL statement. The most direct and popular method is the VALUES clause, which lists numerous data sets, each representing a row.
INSERT … SELECT: INSERT… SELECT in PostgreSQL is a powerful way to populate a table with data from a SELECT query rather than explicitly listed values. This method lets you insert single rows or a dataset comprising numerous SELECT-generated records.
RETURNING clause: Returned values, including auto-generated primary keys, can be returned by the INSERT command with the RETURNING clause.
UPSERT (INSERT … ON CONFLICT DO UPDATE): When there is a conflict, an INSERT can proceed as a UPDATE with UPSERT (INSERT… ON CONFLICT DO UPDATE), which handles situations when a record may already exist.
COPY command: PostgreSQL’s COPY command is efficient for bulk loading and unloading data between a table and a file system or with the client application. It is substantially faster than INSERT statements for high data volumes because to its lower overhead.
UPDATE
The purpose of this statement is to change data that already exists in a table or view.
Features:
SET clause: For the lifetime of a database session, the SET command can also be used to modify different PostgreSQL run-time parameters. These modifications are session-local, which means they don’t touch other ongoing sessions or persist worldwide; they just impact the active connection.
WHERE clause: A key part of SQL statements in PostgreSQL, the WHERE clause is mostly used to filter data from a table or a joined result set according to predetermined criteria. Limiting the output to only the rows for which the condition evaluates to TRUE is its main goal.
Updating multiple columns: Using commas to separate column=value pairs allows multiple column updates in one UPDATE statement.
UPDATE … FROM: The ability to update a table using information from other tables is provided by UPDATE… FROM. This is especially helpful in PostgreSQL because of its non-standard support for JOIN in UPDATE queries.
Writable CTEs: By enabling Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE, and MERGE) to be used inside the WITH clause, PostgreSQL’s Writable Common Table Expressions (CTEs) feature expands the capabilities of basic CTEs.
DELETE
The purpose of this command is to eliminate data rows from a table or view.
Features:
WHERE clause: The WHERE clause in PostgreSQL is essential to SQL statements, filtering rows based on a condition to include only TRUE entries. This SQL expression must return a Boolean result (TRUE, FALSE, or NULL) and appears after the FROM clause in SELECT statements.
DELETE … USING: PostgreSQL’s DELETE… USING clause can remove records from a table based on other tables’ requirements. Unlike a typical DELETE statement, DELETE… USING lets you specify extra tables (or query results) in the USING clause. To set deletion criteria, utilise these tables in the WHERE clause.
TRUNCATE command: The TRUNCATE command is a quicker DDL substitute that works better on huge tables because it doesn’t scan the table. In contrast to DELETE, TRUNCATE cannot be undone if it is not used in a transaction.
DML in PostgreSQL’s Architecture
PostgreSQL uses MVCC for DML statements. Every SQL statement captures a consistent “snapshot” of the database at a certain time, eliminating concurrent update discrepancies. It lowers lock contention and speeds up multi-user configurations. Table-level locks are acquired by DML statements to guard against DDL commands that could impact entire tables, including removing or changing schemas, even if their primary usage is MVCC for concurrency.
There are several interfaces in PostgreSQL that allow DML commands to be executed:
Command-line tools: Direct SQL command execution is possible with the psql interactive terminal client. Command-line editing, SQL interpolation, and variable substitution are among its capabilities.
Graphical administration tools:Although the basic PostgreSQL distribution lacks graphical tools, a variety and commercial choices, such as DBeaver and pgAdmin, facilitate database management, modelling, and SQL creation.
Programming interfaces: PostgreSQL supports ODBC, JDBC, Python, PHP, Ruby, C, C++, Java, Perl, Tcl, and JavaScript (PL/V8).
Procedural Languages (PLs): Procedure-based languages like PgSQL, PL/Tcl, Perl, and Python can build and run server functions. These languages let databases directly implement complex business logic like DML statements. EXECUTE runs dynamic SQL in PL/pgSQL.