Creating a View

Postgres has this concept of a VIEW. The VIEW is an object that stores a query. I’m still learning about Postgres, but I see the VIEW as sort of like shortcut or alias for a query.

You would use a VIEW if maybe you have a long complicated query that you might want to use over time. The creation process is straightforward and the docs are simple enough.

Documentation Example

The example in the docs is a good one. If you happen to have a table called films and you are frequently accessing films that are comedies you could create a view called comedies like this.

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

Then you can write out a query like this

SELECT * FROM comedies;

 \dv

         List of relations
 Schema |    Name    | Type | Owner 
--------+------------+------+-------
 public | comedies | view | alen

Large table with many joins

I have an example where I need many joined tables. The query is long and can be very tedious to constantly be writing it out.

SELECT 
    e0.id, e0.name, e0.slug,
    e1.id, e1.edition, e1.url, e1.date, e1.event_id,
    v2.id, v2.title, v2.language, v2.description, v2.provider, v2.videoid, v2.image_url, v2.event_id, v2.edition_id,
    s3.id, s3.name, s3.twitter, s3.website
FROM 
    events AS e0 
LEFT OUTER JOIN 
    editions AS e1 ON e1.event_id = e0.id
LEFT OUTER JOIN 
    videos AS v2 ON v2.edition_id = e1.id
LEFT OUTER JOIN 
    videos_speakers AS v4 ON v4.video_id = v2.id
LEFT OUTER JOIN 
    speakers AS s3 ON v4.speaker_id = s3.id
ORDER BY 
    e1.date DESC;

Attempt 1

I followed the docs and tried doing this

CREATE VIEW all_events 
AS
    SELECT 
        e0.id, e0.name, e0.slug,
        e1.id, e1.edition, e1.url, e1.date, e1.event_id,
        v2.id, v2.title, v2.language, v2.description, v2.provider, v2.videoid, v2.image_url, v2.event_id, v2.edition_id,
        s3.id, s3.name, s3.twitter, s3.website
    FROM 
        events AS e0 
    LEFT OUTER JOIN 
        editions AS e1 ON e1.event_id = e0.id
    LEFT OUTER JOIN 
        videos AS v2 ON v2.edition_id = e1.id
    LEFT OUTER JOIN 
        videos_speakers AS v4 ON v4.video_id = v2.id
    LEFT OUTER JOIN 
        speakers AS s3 ON v4.speaker_id = s3.id
    ORDER BY 
        e1.date DESC;

I got this error

ERROR: column "id" specified more than once

What this means is that in order to create a VIEW I need to create aliases for anything with x.id.

I need to write out like e0id as event_id.

CREATE VIEW all_events AS
SELECT  e0.id as event_id, --<< here 
        e0.name as event_name, --<< here 
        e0.slug,
        e1.id as edition_id,  --<< here
        e1.edition, 
        e1.url, 
        e1.date, 
        e1.event_id as edition_event_id, --<< here
        v2.id as video_id,  --<< here
        v2.title, 
        v2.language, 
        v2.description, 
        v2.provider, 
        v2.videoid, 
        v2.image_url, 
        v2.event_id as video_event_id, --<< here 
        v2.edition_id as video_edition_id, --<< here
        s3.id as speaker_id,  --<< here
        s3.name as speaker_name, --<< here 
        s3.twitter, 
        s3.website
FROM events AS e0 
  LEFT OUTER JOIN editions AS e1 ON e1.event_id = e0.id
  LEFT OUTER JOIN videos AS v2 ON v2.edition_id = e1.id
  LEFT OUTER JOIN videos_speakers AS v4 ON v4.video_id = v2.id
  LEFT OUTER JOIN speakers AS s3 ON v4.speaker_id = s3.id;