Querying and Inserting | DML
DML stands for Data Manipulation Language and is typically used to add, retrieve or update data. The commands used for DML in PostgreSQL are
In this section, we will try out
- Querying and Manipulating Data with simple select, insert, update, delete statements.
- Filtering and sorting with arguments like where, order_by, limit, offset
Modifying Data in PostgreSQL
INSERT with Postgres
Data can be inserted into Postgres using
INSERT INTO. For example:
INSERT INTO users (id, name, age, is_active)VALUES(1, 'John', 20, true );
UPDATE with Postgres
Existing data in tables can be updated using
UPDATE usersSET is_active = falseWHERE age < 10;
DELETE with Postgres
Similarly to delete row(s) of an existing table, we can execute the following statement.
DELETE FROM usersWHERE id = 1;
Querying Data in PostgreSQL
In the above examples, we saw how to add or modify data. Now lets look at how to query the data.
SELECT with Postgres
SELECT id, name, age, is_active FROM users;
This will give the rows with all the 4 columns of the table selected.
Filtering with Postgres
Lets try out a statement to filter users who are active and above a certain age. The statement would look something like below:
SELECT id, name FROM usersWHERE is_active = true AND age > 13;
Sorting with Postgres
In this example, we fetch users ordered by their name in ascending.
SELECT id, name FROM usersORDER BY name asc;