PostgreSQL Delete Row

In this PostgreSQL tutorial, we will study the use of the Postgresql delete row and we are going to discuss the following list of topics.

• PostgreSQL delete row
• PostgreSQL delete row from table cascade
• PostgreSQL delete row if table exists
• PostgreSQL delete row from select
• PostgreSQL delete row with foreign key constraint
• PostgreSQL delete rows from multiple tables
• PostgreSQL delete row if exists
• PostgreSQL delete row after time
• PostgreSQL delete rows older than
• PostgreSQL delete row limit

PostgreSQL Delete Row

We have a delete query in Postgresql which is used to delete one or more rows from the table. Moreover, it is mandatory to use the WHERE clause with the DELETE query to delete the selected rows else all the existing records will be deleted.

The below syntax is used for the DELETE statement.

DELETE FROM table_name
WHERE condition;

In the above syntax, firstly we will write the name of the table from which we want to delete the data after using DELETE FROM keywords. Then we will use a condition after the WHERE clause to define which rows we want to delete from the table.

Let’s suppose we have a table employee. Now we will apply the delete statement to understand this concept better.

DELETE FROM employee WHERE id = 4;

Let’s check the output of the above query.

We can see in the output that id 4 is deleted from the table using the DELETE statement.

Also, check: PostgreSQL add primary key

PostgreSQL delete row from table cascade

A cascade in Postgresql means to delete or update records in a parent table will immediately delete or update matching records in a child table where a foreign key relationship is in place.

If we want don’t want to delete records from the child table then we will put the foreign key value for the parent table record to NULL.

We can use the DELETE CASCADE statement to ensure that all foreign-key references to a record are deleted when that record is deleted.

The table which has the foreign key in it is called the child table or referencing table. The parent table in Postgresql will refer to the table to which the foreign key is related.

A foreign key in the referencing table or child table is a reference to the primary key in the parent table.

Now we have two tables candidate and process in which we will delete rows from the table cascade. The DELETE statement in Postgresql generally uses a WHERE clause to select rows from the table described.

In the absence of a WHERE clause, all rows in the table get deleted. Let’s see the DELETE CASCADE query below.

DELETE FROM process WHERE SECTION ='sales';

Let’s check the output for the above query.

In the above output, cascade delete takes effect when we deleted the record from the process table where the value of the section is ‘sales’. The cascade ensured that related records from the candidate table has been removed. Because the candidate_id for the record in question is ‘1’, all candidates under process_fk 1 have been deleted.

Also, read: Postgresql Having Clause

PostgreSQL delete row if table exists

The DELETE query in Postgresql deletes rows that satisfy the WHERE clause from the table explained. In the absence of the WHERE clause, the effect is to delete all rows in the table with the valid result and empty tables. Here is the syntax for deleting rows if a table exists.

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP TABLE will discard tables from the database. Only the owner can delete the table. To empty a table of rows without deleting the table, use DELETE. On the other hand, IF EXISTS will not throw an error if the table does not exist. A notice is generated in this case.

We will delete our table process and then we’ll check again if it exists or not. Let’s check its code first.

DROP TABLE IF EXISTS process;
DELETE FROM PROCESS WHERE SECTION = 'sales';

Let’s check the output of the above two queries.

Read: PostgreSQL group by

PostgreSQL delete row from select

In Postgresql, we can delete one row or multiple rows using a SELECT statement. So, we are having a table candidate in which we will delete our first two rows including the SELECT statement. Let’s check the query for deleting the rows using the SELECT statement.

delete from candidate where candidate_id in(select candidate_id from candidate where candidate_id<3);

Let’s check the output of the above query.

We can see from the output that we have deleted the first two rows of the table candidate have been deleted using the SELECT statement.

Read: Drop Database PSQL

PostgreSQL delete row limit

Postgresql provides a feature of deleting one or multiple rows at once by using the LIMIT clause. This clause is generally used to get a subset of rows that are generated by the query.

Basically, the LIMIT clause will retrieve only the number of records described after the LIMIT keyword is used unless the query itself returns limited records than the number described by LIMIT.

Let’s take an example of the table candidate in which we will delete the first four rows at once by using the below query.

DELETE FROM candidate
WHERE candidate_id IN (
    SELECT candidate_id
    FROM candidate
    ORDER BY status
    LIMIT 4);

Let’s check the output now.

We can clearly see in the output that the first four rows are deleted by using the LIMIT clause in the query.

Read: PostgreSQL Loop Examples

PostgreSQL delete row with foreign key constraint

Postgresql also provides deleting the row with a foreign key constraint means we have to delete the foreign key with its constraints.

We can’t disable a foreign key constraint in Postgresql. But, we can discard the foreign key constraint from a column and then re-add it to the column. So we will use the following query.

DELETE from candidate where process_fk = 2;

Let’s check the output of the above query.

We can clearly see from the output that we have deleted the foreign key with values in the foreign key column which is process_fk.

Read: Postgresql Joins

PostgreSQL delete rows if exists

If we are deleting rows from our table, we don’t need to use IF EXISTS because we are already using a WHERE clause, so if it exists it will be deleted.

We have already discussed this in the very first topic Postgresql delete row. Let’s understand this with the help of an example.

We have a table candidate and we want to delete candidate_id=5. After applying the delete command we can clearly see that the candidate_id=5 is deleted from the table.

So there is no need of using IF EXIST for checking the existence of the deleted row, we can check it using the SELECT statement.

Let’s check the code for deleting the row.

DELETE FROM candidate WHERE candidate_id =5;

Let’s check the output.

Read: PostgreSQL Min

PostgreSQL delete row after time

In Postgresql, there is no such feature of deleting rows on particular time-based management. We can run only daily based cron-jobs to use simple DELETE commands. In Postgresql, a cron-based job scheduler works inside the database as an extension.

It is essentially a backend worker that initiates some basic commands according to their time management by connecting to the local database as the user who has scheduled that particular job.

PostgreSQL delete rows from multiple tables

In Postgresql, the DELETE command will delete rows that specify the WHERE clause from the described table. If the WHERE clause is not present, the effect is to delete all rows within the table.

The output will be valid with an empty table. We can also delete rows from multiple tables in Postgresql by creating two tables of Let’s check the code

create table users (
id int primary key,
name text,
address text);

create table orders (
orderid int primary key,
userid int references users (id) on delete cascade,
orderdate date,
total numeric);

DELETE
FROM orders o
USING users u
WHERE o.userid = u.id
and u.name = 'Jack';

DELETE
FROM users u
WHERE u.name = 'Jack';

Let’s check the output.

Read: PostgreSQL Update Join

PostgreSQL delete rows older than

In Postgresql, if the table is big then we will do a vacuum full analysis of the table (Unitedstates) the first time we discard old data or reload the table to restore space & update the query planner.

In the below query UnitedStates is the table name and acctstoptime is the row and 30 days is the time given for deleting the 30 days older data. Below is the query.

DELETE FROM unitedstates WHERE acctstoptime < (now() - '30 days'::interval);

Let’s check the output.

You may also like to read the following blogs on PostgreSQL.

• Postgresql Add Foreign Key
• PostgreSQL Subquery
• Postgresql replace
• Postgresql For Loop + Examples
• Postgresql unique constraint
• PostgreSQL Like With Examples

In this tutorial, we have studied the use of the Postgresql delete row and we have discussed the following list of topics.

• Postgresql delete row
• Postgresql delete row from table cascade
• Postgresql delete row if table exists
• Postgresql delete row from select
• Postgresql delete row with foreign key constraint
• Postgresql delete rows from multiple tables
• Postgresql delete row if exists
• Postgresql delete row after time
• Postgresql delete rows older than
• Postgresql delete row limit