PostgreSQL DML (Data manipulation language ) statements to access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. Some of the important data manipulation language statements are:
A SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data stored in the database, although it can manipulate the accessed data before returning the results of the query.
In the postgreSQL, you can enter a DML statement after the sql prompt.
The effect of a DML statement is not permanent until you commit the transaction that includes it. A transaction is a sequence of SQL statements that Oracle Database treats as a unit (it can be a single DML statement). Until a transaction is committed, it can be rolled back (undone). For more information about transactions,
Table of Contents
PostgreSQL DML includes
INSERT: insert a record into the table.
SELECT: Retrieves the records from the table.
UPDATE: update the existing record with the new values.
DELETE: delete a record from the table.
UPSERT: upsert in nothing but merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, PostgreSQL inserts the new row.
PostgreSQL insert query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
testdb=> testdb=> select * from dept; deptno | job | deptaddress --------+-----------+------------- 20 | CLERK | CANADA 30 | SALESMAN | NY 10 | PRESIDENT | LA (3 rows) testdb=> testdb=> testdb=> insert into dept values('40','AUDITOR','LV'); INSERT 0 1 testdb=> |
PostgreSQL select query
|
1 2 3 4 5 6 7 8 9 10 |
testdb=> select * from dept; deptno | job | deptaddress --------+-----------+------------- 20 | CLERK | CANADA 30 | SALESMAN | NY 10 | PRESIDENT | LA 40 | AUDITOR | BY (4 rows) testdb=> |
PostgreSQL update query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
testdb=> testdb=> select * from dept; deptno | job | deptaddress --------+-----------+------------- 20 | CLERK | CANADA 30 | SALESMAN | NY 10 | PRESIDENT | LA 40 | AUDITOR | LV (4 rows) testdb=> update dept set deptaddress='BY' where deptno=40; UPDATE 1 testdb=> testdb=> commit; WARNING: there is no transaction in progress COMMIT |
PostgreSQL delete query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
testdb=> delete from dept where deptno=40; DELETE 1 testdb=> commit; WARNING: there is no transaction in progress COMMIT testdb=> testdb=> select * from dept; deptno | job | deptaddress --------+-----------+------------- 20 | CLERK | CANADA 30 | SALESMAN | NY 10 | PRESIDENT | LA (3 rows) testdb=> |
PostgreSQL upsert
syntax
The target can be:
(column_name) – a column name.
ON CONSTRAINT constraint_name – where the constraint name could be a name of the UNIQUE constraint.
WHERE predicate – a WHERE clause with a predicate
The action can be:
DO NOTHING – means do nothing if the row already exists in the table.
DO UPDATE SET column_1 = value_1, .. WHERE condition – update some fields in the table.
|
1 2 3 4 5 6 7 8 9 10 11 |
testdb=> select * from dept; deptno | job | deptaddress --------+-----------+------------- 20 | CLERK | CANADA 30 | SALESMAN | NY 10 | PRESIDENT | LA (3 rows) testdb=> insert into dept values(10,'DEVELOPER','BY') on conflict(deptno) do nothing; INSERT 0 0 testdb=> |
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
