ruk·si

🐘 PostgreSQL
Views

Updated at 2015-11-19 10:38

Views are like pre-defined SELECT queries that are stored in the database. Compared to materialized views, the underlying view query is executed every time the views is used.

Related to:

-- DROP VIEW my_view;
CREATE VIEW my_view AS
    SELECT
          w.city,
          w.temp_lo,
          w.temp_hi,
          w.date,
          c.location
     FROM weather w, cities c
    WHERE w.city = c.name;

SELECT * FROM my_view;
-- DROP VIEW mary_users;
CREATE OR REPLACE VIEW mary_users AS
  SELECT *
  FROM users
  WHERE name ILIKE '%mary%';

SELECT * FROM mary_users;

Consider dropping all related views when altering a table. For example, old column names will stay stored in the view. Expected and wanted behavior but sometimes annoying.

Sources