Changing data and structure

This lesson covers INSERT, UPDATE / DELETE, CREATE VIEW, CHECK constraint, CREATE INDEX, BEGIN / COMMIT, CREATE PROCEDURE. Read it, then solve questions 61–70 below it in the sidebar. Every query runs on a real PostgreSQL database inside your browser.

Every run starts fresh

In this section your SQL changes the database. Each Run or Submit starts from a fresh copy of the store, so experiment freely. The checker then runs its own queries (listed on the question) to see what your SQL did.

INSERT, UPDATE, DELETE

• INSERT INTO t (cols) VALUES (…), (…); adds rows. INSERT INTO t SELECT … adds the rows a query returns.
• UPDATE t SET col = expr WHERE …; changes rows. Forgetting WHERE updates every row.
• DELETE FROM t WHERE …; removes rows.

A quote inside text is doubled: 'Rohan''s'.

UPDATE customers SET city = 'Navi Mumbai' WHERE customer_id = 6;
INSERT INTO customers VALUES (7, 'D''Souza', 'Goa', '2024-03-30');
SELECT * FROM customers;

Views

CREATE VIEW name AS SELECT …; saves a query under a name. Querying the view runs the saved query on the current data. Good for hiding a complex join behind a simple name.

CREATE VIEW mumbai_customers AS
SELECT customer_id, customer_name FROM customers WHERE city = 'Mumbai';

SELECT * FROM mumbai_customers;

Constraints

Constraints are rules the database enforces: PRIMARY KEY, FOREIGN KEY … REFERENCES, NOT NULL, UNIQUE, CHECK (condition). Add one later with ALTER TABLE t ADD CONSTRAINT name CHECK (…);. Any insert or update that breaks it fails with an error.

ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (unit_price > 0);
INSERT INTO products VALUES (120, 'Freebie', 'Snacks', 0);

Indexes

CREATE INDEX name ON table (column); builds a sorted lookup structure (a B-tree) so filters and joins on that column don’t have to scan the whole table. They speed up reads and slightly slow down writes. Primary keys get one automatically.

CREATE INDEX idx_customers_city ON customers (city);
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customers';

Transactions

BEGIN; … COMMIT; makes several statements atomic: all of them take effect, or (on an error, or with ROLLBACK;) none do. START TRANSACTION is the same as BEGIN.

BEGIN;
DELETE FROM sales WHERE customer_id = 5;
SELECT COUNT(*) AS during FROM sales;
ROLLBACK;
SELECT COUNT(*) AS after_rollback FROM sales;

Stored procedures

A procedure is named, stored code you run with CALL. In PostgreSQL:

CREATE PROCEDURE name(p_x INT)
LANGUAGE plpgsql
AS $$
BEGIN
  -- statements using p_x
END;
$$;

$$ … $$ is just a way to quote the body without escaping. Prefix parameters (p_) so they don’t clash with column names.

CREATE PROCEDURE rename_city(p_old TEXT, p_new TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE customers SET city = p_new WHERE city = p_old;
END;
$$;

CALL rename_city('Mumbai', 'Bombay');
SELECT customer_name, city FROM customers;