When we want to remove rows, we can use the
DELETE statement. We can remove all rows in a table, or use selectors like WHERE and LIMIT to remove less. In this article, we will learn how to use DELETE in Postgresql.
For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called
version: '3' services: db: image: 'postgres:latest' ports: - 5432:5432 environment: POSTGRES_USER: username POSTGRES_PASSWORD: password POSTGRES_DB: default_database volumes: - psqldata:/var/lib/postgresql phpmyadmin: image: phpmyadmin/phpmyadmin links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: psqldata:
We can run this file, we can use
docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.
You can then login by leaving the host empty and using the following credentials.
POSTGRES_USER: username POSTGRES_PASSWORD: password
In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.
We will be using the sample db provided here: https://dev.mysql.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.
Next, let's create an
CREATE TABLE actor ( actor_id smallint, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (actor_id) ) ;
And finally, let's enter a few rows.
INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'), (2,'NICK','WAHLBERG','2006-02-15 04:34:33'), (3,'ED','CHASE','2006-02-15 04:34:33'), (4,'JENNIFER','DAVIS','2006-02-15 04:34:33'), (5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'), (6,'BETTE','NICHOLSON','2006-02-15 04:34:33'), (7,'GRACE','MOSTEL','2006-02-15 04:34:33'), (8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33')
The basic sql structure as the following pattern.
DELETE FROM actor
Running this command will delete all the rows in a table. Also not, we don't include any column names, because the delete remove rows, not columns.
If we want to only delete select rows, which is more common, we can combine the
DELETE statement with
For example, if we want to delete a single row by id we can use the following.
DELETE FROM actor where actor_id = 1;
This will delete only one row. We can combine the DELETE command with any of our usual filters. Here is another example to delete multiple rows.
DELETE FROM actor where actor_id > 3 and actor_id < 6;
If we have a large table, we may want to use the TRUNCATE statement. This allows Postgresql to skip some steps and drop the whole table. Internally Postgresql will drop all rows then recreate a table. This is very good when you have a large table you want to remove.