PostgreSQL CRUD Operations

For using any system, the major function that must be needed to be present is basic operations like reading and writing of data and creation and alteration of the schemas that store the data in that particular system.

Similarly in a PostgreSQL database, we need similar operations called CRUD operations. CRUD stands for Create, Read, Update and Delete operations in a PostgreSQL database. So, for each operation in the PostgreSQL database, we have a dedicated query to perform those operations. So, in this article, we are going to see the working and syntax of those queries.

PostgreSQL Insert

Insert command in the PostgreSQL is used to insert new records or rows in the tables. Single or multiple rows can be added to the table according to the need of the problem statement.

So let us see the syntax of the insert query, the syntax for the insert query is:

 INSERT INTO TABLE_NAME  
 (column1,  
 column2,  
 column3, ……columnN)   
 VALUES (value1, value2, value3, ….. valueN);

So let us understand it by taking by an example.

Here we have created a table named ‘student’ having two columns name and age. Now with the help of the “Insert” command, we have added one row of data inside the student table and it is visible in the image.

We can also add multiple rows within a single command.

Here in the above image, we have inserted two rows simultaneously with the help of the insert command. We can see that two rows are successfully added to the student table.

We can also insert the default values into the table using this command. The default values will correspond to the data type of that particular column of the table.

As we can see here in the image, after adding the rows with the help of the default keyword, a blank row is added to the table. Another row is added in the table to highlight the blank row inserted by the use of the default keyword.

We can also add data to a particular column in a table using the insert command. We have to specify the column in which we want to add the data.

As shown in the image, we have indeed data only in the name column of the student table and it’s visible that the age column for that particular row is blank that means only the data for the name column is added in that particular query.

PostgreSQL Select

PostgreSQL Select command is used to retrieve data that is stored in a particular table.

We can easily use various clauses along with the select command to get data according to our problem statement.

The several clauses of the SELECT command are as follows:

ORDER BY clause: Sort rows present within the table.
GROUP BY clause: Group rows into the different groups.
WHERE clause: Filter the rows present within the table
HAVING clause: Filter the groups within the table
DISTINCT operator: Select separate rows present within the table.
UNION, INTERSECT, and EXCEPT: Perform set operations on the rows present within the table.
LEFT JOIN, INNER JOIN, CROSS JOIN, FULL OUTER JOIN conditions: Join with other tables with the use of joins.

Let us understand it more clearly with the help of this example. The syntax for the select command is:

 SELECT select_list 
 FROM table_name;

Here we have displayed all the rows present inside the student table with the help of the insert command.

To display a particular column of a table, add the name of the column instead of the * in the previous query and only the data of that particular column will be displayed.

As shown only the name column of the student table is displayed.

PostgreSQL Update

The update query is used to change the value of the already present record in the table. To update a particular value inside a table, we need to use the where clause with it. The syntax for the update command is:

 UPDATE table_name   
 SET column1 = value1,  
 column2 = value2....,  
 columnN = valueN   
 WHERE
 condition;

In this example, we have updated the age column of the row where the name is “Samuel”,and it gets successfully updated.

PostgreSQL Delete

The PostgreSQL Delete command is used to delete the records present in the table permanently.

By default, the delete command will delete all the records in the table but it can be combined with the where clause to delete some specific records.

The syntax for the delete command is:

 DELETE FROM table_name   
 WHERE [condition];

As shown in the image all the records in the student table are deleted using the delete query.

So, with the help of this article, we are able to understand the usage of some of the basic PostgreSQL queries.