Update Data
Update a Row
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 fleet
SET length = 8
WHERE name = 'a-wing';
SELECT *
FROM fleet;
Update Multiple Rows
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 fleet
SET length = 8
WHERE name LIKE '%wing%';
SELECT *
FROM fleet;
Update a Row (& return modified row)
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 fleet
SET length = 8
WHERE name = 'a-wing'
RETURNING *;
Delete Data
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 fleet
WHERE 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.
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 steps
BEGIN;
UPDATE fleet
SET length = length + 1
WHERE name = 'a-wing';
DELETE FROM fleet
WHERE 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.
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 fleet
SET length = length + 1
WHERE name = 'a-wing';
DELETE FROM fleet
WHERE name = 'x-wing';
ROLLBACK;
SELECT *
FROM fleet;
Update a Table
Add a new column
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 fleet
ADD COLUMN mass INTEGER
-- setting a default value (optional)
DEFAULT 10;
SELECT *
FROM fleet;
Remove a column
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 fleet
DROP COLUMN length;
SELECT *
FROM fleet;
Change a column's name
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 fleet
RENAME COLUMN name TO ship_name;
SELECT *
FROM fleet;
Delete a Table
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_name
FROM information_schema.tables;