Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Removing Rows with DELETE

Existing row data within PostgreSQL can be removed with the standard SQL DELETE command. Unless carefully working within transaction blocks, removal via the DELETE command is permanent, and extreme caution should therefore be taken before attempting to remove data from your database.

The syntax to remove one or more rows from a table is as follows:

  DELETE FROM [ ONLY ] table
         [ WHERE condition ]

DELETE FROM [ ONLY ] table

The ONLY keyword may be used to indicate that only the table table should have rows removed from it, and none of its sub-tables. This is only relevant if table is inherited by any other tables.

WHERE condition

The WHERE clause describes under what condition to delete rows from table. If unspecified, all rows in the table will be deleted.

The WHERE clause is almost always part of a DELETE statement. It specifies which rows in the target table are to be deleted based on its specified conditions, which may be expressed syntactically in the same form as in the SELECT statement.

It is a good habit to execute a SELECT statement with the intended WHERE clause for your DELETE statement. This allows you to review the data to be deleted before the DELETE statement is actually executed. This technique and a simple DELETE statement are demonstrated in Example 4-57.

Example 4-57. Deleting rows from a table

booktown=# SELECT * FROM stock
booktown-#          WHERE stock = 0;
    isbn    | cost  | retail | stock
------------+-------+--------+-------
 0394800753 | 16.00 |  16.95 |     0
 0394900014 | 23.00 |  23.95 |     0
 0451198492 | 36.00 |  46.95 |     0
 0451457994 | 17.00 |  22.95 |     0
(4 rows)

booktown=# DELETE FROM stock
booktown-#        WHERE stock = 0;
DELETE 4

If a WHERE condition is not specified, the DELETE command removes all rows within that table, as shown in Example 4-58.

Example 4-58. Deleting all table rows

booktown=# DELETE FROM stock_backup;
DELETE 16

 
 
  Published courtesy of O'Reilly Design by Interspire