View Error in Postgresql

View error in PostgreSQL

That happens because a view would have two id named columns, one from table1 and one from table2, because of the select *.

You need to specify which id you want in the view.

SELECT table1.id, column2, column3, ... FROM table1, table2 
WHERE table1.id = table2.id

The query works because it can have equally named columns...

postgres=# select 1 as a, 2 as a;
 a | a
---+---
 1 | 2
(1 row)

postgres=# create view foobar as select 1 as a, 2 as a;
ERROR:  column "a" duplicated
postgres=# create view foobar as select 1 as a, 2 as b;
CREATE VIEW

PostgreSQL view errors

JayC and JNevill gave you your answer in the comments. You can't have two columns with names that are the same when creating views.

They said that you can't do a SELECT *, but rather would have to do something like

SELECT con.id AS contact_id, cust.id AS customer_id, etc.

Something that I'd like to add is that if you want to be able to use SELECT * for creating a view, then when you create your tables you can avoid generic terms like id and instead your contacts table could have a contact_id column and your customers table could have a customer_id column. Then you don't run into the issue.

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

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.

User Permissions error for reading from view in postgres

You can use CREATE OR REPLACE VIEW to change a view definition if you don't change its columns.

In PostgreSQL, permissions are stored on the object. If you drop an object, all its permissions are gone. If you later create an object with the same name, that is still a different object and will have the default permissions.

You can use ALTER DEFAULT PRIVILEGES to grant permissions for objects that are created in the future.

Error with creating view in PostgreSQL

Label should not to start by number. Use double quotes or rename label

postgres=# select 10 as 2014_some;
ERROR:  syntax error at or near "2014"
LINE 1: select 10 as 2014_some;
                     ^
Time: 0.647 ms
postgres=# select 10 as "2014_some";
 2014_some 
───────────
        10
(1 row)

Function to create a view not working in postgresql - ERROR: column does not exist

I have looked all over and cant find an example which does this. the examples I found is all to run a static query from a table. Not get an single example using a function parameter.

Because a View IS a "static query from a table (or tables)"
- and they do not accept parameters

What you are attempting, to use a function to create a view is just wrong.

Your use of naming conventions are odd also. The NAME cc_getbalancesfordate_vw belongs to a VIEW (_vw)

---

Related Topics