CODEWITHSUNDEEP
postgresqlsql-view
geetika
geetika

Reputation: 29

I am not able to create view in PostgreSQL

I am trying to create a view in Postgres. I am using Dbeaver to do so. my query is as follows:

CREATE VIEW customer_master as
(
select * 
from survey_info 
  full join survey_responses on survey_info.submissionid =survey_responses.submissionid 
);

It's throwing an error :

CREATE VIEW customer_master as SQL Error [42701]: ERROR: column "submissionid" specified more than once.

Anyone faced such an issue?

Upvotes: 3

Views: 5770

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

If you have common columns between joined table, it is required to specify all the columns required using proper aliases.

CREATE VIEW customer_master as
select info.submissionid as submissionid_1,
       resp.submissionid as submissionid_2, --Keep one or use 
                                            --coalesce if one is null 
                                --i.e  coalesce(info.submissionid,resp.submissionid)
       info.col2,
       info.col3,
       resp.col2,
       resp.col3
                 --other columns with aliases
 from survey_info info 
   full join survey_responses resp
  on info.submissionid =resp.submissionid

Upvotes: 4

Related Questions