SQLDailyDrills

ALTER TABLE, CREATE VIEW and CREATE INDEX

Change a table's structure, save a query under a name, and speed up lookups.

Syntax

ALTER TABLE name ADD COLUMN column type;
ALTER TABLE name ADD CONSTRAINT rule CHECK (condition);
CREATE VIEW name AS SELECT …;
CREATE INDEX name ON table (column);
DROP TABLE [IF EXISTS] name;
  • A view stores no data; it runs its query each time.
  • Primary key and unique constraints get an index automatically.

Example

SELECT indexname FROM pg_indexes WHERE tablename = 'orders' ORDER BY indexname;

The indexes on orders: the one PostgreSQL made for the primary key.

Tables used: orders. Try it in a lesson below.

Common mistakes

  • DROP TABLE cannot be undone outside a transaction.
  • Every extra index makes inserts and updates a little slower.

Practise it