Updated May 10, 2023
Introduction to PostgreSQL Queries
PostgreSQL supports the standard SQL to query the data or information. PostgreSQL Queries can access multiple tables at once or can process multiple rows at the same time of the same table. Rows can be selected, updated, or removed using the queries.
PostgreSQL Queries with Examples
Following are the queries of PostgreSQL, and examples are given below. To understand the SQL queries, let’s create a table ‘student’:
1. CREATE
Syntax:
CREATE TABLE student (
rollno INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
branch VARCHAR(30) NOT NULL,
result VARCHAR(30) NOT NULL,
joining_date VARCHAR(30) NOT NULL,
PRIMARY KEY (rollno)
);2. INSERT
The INSERT statement allows us to insert one or more rows into the table at a time.
Insert Single Row
Syntax:
INSERT INTO table (column_1, column_2, …)
VALUES
(value_1, value_2, …);Explanation: To use the above command, we must provide the table name with a list of column names separated by commas after the INSERT INTO clause. And then give a list of values separated by commas after the VALUES clause.
INSERT INTO student (rollno, firstname, lastname, branch, result, joining_date)
VALUES ('101', 'Harry','Robert', 'Computer', true, '2019-12-31');Illustrate the inserted rows with the SELECT statement.
select * from student
Add Multiple Rows at a Time
Syntax:
INSERT INTO table (column_1, column_2, …)
VALUES
(value_1, value_2, …),
(value_1, value_2, …) ,...;Explanation: Adding multiple rows into a table is the same as inserting a single row in the table; we only need to provide extra values list separated by commas after the first list.
INSERT INTO student (rollno, firstname, lastname, branch, result, joining_date)
VALUES ('101', 'Harry','Robert', 'Computer', true, '2019-12-31'),
('102', 'Jacob','Michael', 'Mechanical', false, '2019-12-30'),
('103', 'Charlie','William', 'Electrical', true, '2019-12-29'),
('104', 'Thomas','David', 'Civil', false, '2019-12-28');Illustrate the inserted rows with the SELECT statement.
select * from student
Insert Data from another Table
Syntax:
INSERT INTO table(column1,column2,...)
SELECT column1,column2,...
FROM another_table
WHERE condition;Let’s create a new table named ‘temp_student’ to understand.
CREATE TABLE temp_student (LIKE student);
INSERT INTO temp_studentIllustrate the inserted rows with the SELECT statement.
select * from temp_student
3. SELECT
Syntax:
SELECT column_name_1, column_name_2, ….
FROM table_name [WHERE condition];Explanation:
- Provide the column names after the SELECT keyword. Use a comma-separated list to provide multiple column names in case you want to retrieve data for more than one column. Use an asterisk (*) to retrieve data for all columns.
- After the FROM clause, provide the table name.
Query Data from One Column
Find the roll nos of all students in the student table:
SELECT rollno FROM student;
Query Data from Multiple Columns
You can specify interesting columns roll no, first name, and last name after the SELECT keyword, as shown in the following query:
SELECT rollno , firstname, lastname FROM student;
Select Statement with Expressions
Return the combination of ‘firstname’ and ‘lastname’ separated by a space as ‘fullname’ and the results of all students:
SELECT
firstname
||
' '
||
lastname AS fullname,
result
FROM
Student;
The result of the above statement:
The SELECT Statement with LIMIT clause
LIMIT is optional of the SELECT statement, which returns a subset of rows.
SELECT
rollno , firstname, lastname
FROM
student
LIMIT 2;The result of the above statement:
4. UPDATE
Syntax:
UPDATE table_name
SET column_1 = value_1,
column_2 = value_2 ,...
WHERE
Condition;Explanation:
- Provide the name of the table after the UPDATE clause.
- Provide the list of names of the columns to be modified in the SET clause.
- Add a condition to filter rows in the WHERE clause.
- We can update all rows by omitting the WHERE clause.
UPDATE table Partially
Change the result as true for all rollno which are greater and equal to 103:
UPDATE student SET "result" = true WHERE rollno >= 103;
select * from student;The result of the above statement:
UPDATE all Rows
Update the values of the result column to true for all rows.
UPDATE student SET "result" = true
select * from student;The result of the above statement:
UPDATE with Returning Clause
We can return the updated entries using the RETURNING clause in the UPDATE statement.
Update ‘rollno’ 101 rows and returns the updated rows:
UPDATE student SET firstname = 'Bruce', lastname = 'Lee' WHERE rollno = 101
RETURNING rollno , firstname, lastname;The result of the above statement:
6. DELETE
Syntax:
DELETE FROM table_name WHERE condition;Explanation:
- Provide the table name after the DELETE FROM clause.
- Add a condition using the WHERE clause to decide which rows to delete. The DELETE statement without a WHERE condition will delete all rows.
With the help of the USING clause, we can check the reference condition to one or many columns in another table.
DELETE FROM
table_name
USING
another_table_name
WHERE
table_name.<col_name> = another_table_name.<col_name> AND …If you do not want to use the USING, you can use the subquery as below:
DELETE FROM
table_name
WHERE
table_name.<col_name> = (SELECT <col_name> FROM another_table_name);We will use the student and ‘temp_student’ tables created in the data INSERT section.
Query to DELETE with a WHERE clause
Deleting the rows whose value in the role column is 103:
DELETE FROM student
WHERE rollno = 103;Illustrate the result using the SELECT statement:
SELECT * FROM student;
Query to DELETE with USING
Delete all rows from the ‘student’ table that have the rollno columns’ values in the ‘temp_student’ table.
Let’s illustrate the ‘temp_student’ table.
SELECT * FROM temp_student;
Roll no 101 should be removed ‘student’ table
DELETE FROM student
USING temp_student
WHERE student.rollno = temp_student.rollno;Illustrate the results using the SELECT statement:
SELECT * FROM student;
Query to DELETE all Rows
DELETE FROM student;Illustrate the results using a SELECT statement:
SELECT * FROM student;Delete rows from the temp_student table and return the deleted rows:
DELETE FROM temp_student
RETURNING *;Result of the above statement:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Queries” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
