CODEWITHSUNDEEP
sqldatabasepostgresqljoinsql-view
alenm
alenm

Reputation: 1043

Creating Postgres View getting ERROR: column "id" specified more than once

SCENARIO:

I have this select statement that JOINs a bunch of tables together:

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'd like to create a Postgres View. So wrote it out like 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 keep getting this error:

ERROR: column "id" specified more than once

QUESTIONS:

  1. How do I fix this error? I would like to create a view called "all_events".
  2. Are Postgres View sort of like aliases in other languages?

New to Postgres, reading the docs but trying to understand the mental model here.

Upvotes: 1

Views: 4192

Answers (2)

user330315
user330315

Reputation:

You have several column names that are the same. Even if you select e0.id the column is still name (only) id.

But in the scope of a view (or table) each column name must be unique.

You need to provide aliases for each duplicate column:

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;

Although Postgres allows it, I highly recommend to not create a view with an ORDER BY statement. If you ever sort the results of that view by a different column, Postgres will sort the data twice.

Upvotes: 6

Andronicus
Andronicus

Reputation: 26036

In the view columns are by default named same as columns of query. The error occurs, because column named id is defined in all 3 joined tables. You need to provide aliases to differentiate colliding columns, for example:

CREATE VIEW all_events AS
SELECT e0.id as e0id, e0.name as e0name, e0.slug,
        e1.id as e1id, e1.edition, e1.url, e1.date, e1.event_id,
        v2.id as e2id, v2.title, v2.language, v2.description, v2.provider, v2.videoid, v2.image_url, v2.event_id, v2.edition_id,
        s3.id as s3id, s3.name as s3name, 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;

Upvotes: 1

Related Questions