CODEWITHSUNDEEP
postgresql
user2635911
user2635911

Reputation: 492

PostgreSql unable to create view due to "duplicate column"

I am trying to create a country_name, and country cid pair between each country that are neighbours:

Here's the schema:

CREATE TABLE country (
    cid         INTEGER     PRIMARY KEY,
    cname       VARCHAR(20) NOT NULL,
    height      INTEGER     NOT NULL,
    population  INTEGER     NOT NULL);

CREATE TABLE neighbour (
    country     INTEGER     REFERENCES country(cid) ON DELETE RESTRICT,
    neighbor    INTEGER     REFERENCES country(cid) ON DELETE RESTRICT, 
    length      INTEGER     NOT NULL,
    PRIMARY KEY(country, neighbor));

My query:

create view neighbour_pair as (
select c1.cid, c1.cname, c2.cid, c2.cname
from neighbour n join country c1 on c1.cid = n.country
join country c2 on n.neighbor = c2.cid);

I am getting error code 42701 which means that there is a duplicate column.

The actual error message I am getting is:

ERROR:  column "cid" specified more than once

********** Error **********

ERROR: column "cid" specified more than once
SQL state: 42701

I am unsure how to go around the error problem since I WANT the pair of neighbour countries with the country name and their cid.

Upvotes: 2

Views: 8082

Answers (3)

Jeffrey James
Jeffrey James

Reputation: 235

I ran into a similar issue recently. I had a query like:

CREATE VIEW pairs AS

SELECT p.id, p.name, 
    (SELECT count(id) from results
            where winner = p.id),
    (SELECT count(id) from results
            where winner = p.id OR loser = p.id) 
FROM players p LEFT JOIN matches m ON p.id = m.id
GROUP BY 1,2;

The error was telling me: ERROR: column "count" specified more than once. The query WAS working via psycopg2, however when I brought it into a .sql file for testing the error arose.

I realized I just needed to alias the 2 count subqueries:

CREATE VIEW pairs AS
    SELECT p.id, p.name, 
        (SELECT count(id) from results
                where winner = p.id) as wins,
        (SELECT count(id) from results
                where winner = p.id OR loser = p.id) as matches 
    FROM players p LEFT JOIN matches m ON p.id = m.id
    GROUP BY 1,2;

Upvotes: 1

Gerardo
Gerardo

Reputation: 1

You can use alias with AS:

For example your view could be as follows:

create view neighbour_pair as 
(
select c1.**cid**
     , c1.cname
     , c2.**cid AS cid_c2**
     , c2.cname
from neighbour n 
join country c1 on c1.cid = n.country
join country c2 on n.neighbor = c2.cid
);

Upvotes: 0

user2635911
user2635911

Reputation: 492

Nevermind. I edited the first line of the query and changed the column names

create view neighbour_pair as 

select c1.cid as c1cid, c1.cname as c1name, c2.cid as c2cid, c2.cname as c2name
from neighbour n join country c1 on c1.cid = n.country
join country c2 on n.neighbor = c2.cid;

Upvotes: 1

Related Questions