Table of Contents

Update Data
Update a Row
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);UPDATE fleetSET length = 8WHERE name = 'a-wing';SELECT *FROM fleet;
Update Multiple Rows
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);UPDATE fleetSET length = 8WHERE name LIKE '%wing%';SELECT *FROM fleet;
Update a Row (& return modified row)
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);UPDATE fleetSET length = 8WHERE name = 'a-wing'RETURNING *;
Delete Data
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);DELETE FROM fleetWHERE name = 'a-wing';SELECT *FROM fleet;
Transaction
A transaction bundles multiple steps into a single, all-or-nothing operation. If some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. The intermediate states between the steps are not visible to other concurrent transactions. Note, the transaction being referred to here is also known as a transaction block. Technically, a single database operation (such as delete a row) is also classified as a transaction.
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);-- a transaction with 2 stepsBEGIN;UPDATE fleetSET length = length + 1WHERE name = 'a-wing';DELETE FROM fleetWHERE name = 'x-wing';COMMIT;SELECT *FROM fleet;
Rollback
We can undo a transaction using the ROLLBACK
command.
This can only be done when a transaction hasn't been committed.
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);BEGIN;UPDATE fleetSET length = length + 1WHERE name = 'a-wing';DELETE FROM fleetWHERE name = 'x-wing';ROLLBACK;SELECT *FROM fleet;
Update a Table
Add a new column
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);ALTER TABLE fleetADD COLUMN mass INTEGER-- setting a default value (optional)DEFAULT 10;SELECT *FROM fleet;
Remove a column
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);ALTER TABLE fleetDROP COLUMN length;SELECT *FROM fleet;
Change a column's name
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);ALTER TABLE fleetRENAME COLUMN name TO ship_name;SELECT *FROM fleet;
Delete a Table
/query.sql
result
CREATE TABLE IF NOT EXISTS fleet (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,class VARCHAR (50) NOT NULL,length INTEGER NOT NULL);INSERT INTO fleet(name, class, length)VALUES ('a-wing', 'fighter', 7),('cruiser', 'corvette', 127),('x-wing', 'fighter', 13),('y-wing', 'bomber', 23);CREATE TABLE IF NOT EXISTS personnel (id serial PRIMARY KEY,name VARCHAR (50) NOT NULL,starship INTEGER,related_to INTEGER);INSERT INTO personnel(name, starship, related_to)VALUES ('Luke', 3, 3),('Obi-Wan', 1, null),('Leia', null, 1);DROP TABLE IF EXISTS fleet;SELECT table_nameFROM information_schema.tables;