SQL creating a view with a temp table

user_15944535 0
Viewed 228

I need some help with creating a view from a temp table. I am fairly new to SQL so I do not understand the error message that is being sent to me when I try to create a view.

Error Message:

SQL Error [42P07]: ERROR: relation "percent_pop_vax" already exists.

I haven't created a view from a temp table so I do not know why this is happening. I have used the CTE method and that seems to work. Please help me understand how to use temp tables for the view. Thank you!

-- Using Temp Table to perform Calculation on Partition By in the previous query.
DROP Table if exists percent_pop_vax
Create Table percent_pop_vax(Continent varchar(255),
    Location varchar(255), Date date, Population numeric,
    New_vaccinations numeric, rolling_vaccinations numeric)
Insert into percent_pop_vax
Select dea.continent, dea.location, dea.date, dea.population, 
    vax.new_vaccinations, SUM(vax.new_vaccinations)
OVER(Partition by dea.Location Order by dea.location, dea.Date
) as rolling_vaccinations
From public.Covid_deaths dea
Join public.Covid_vax vax
    On dea.location = vax.location and dea.date = vax.date
where dea.continent is not null

Select *, (rolling_vaccinations/population)*100 AS
    rolling_vax_for_populations
From percent_pop_vax

-- Creating View to store data for later visualizations
CREATE VIEW percent_pop_vax AS
Select dea.continent, dea.location, dea.date, dea.population, 
    vax.new_vaccinations, SUM(vax.new_vaccinations)
OVER(Partition by dea.Location Order by dea.location, dea.Date)
    as rolling_vaccinations
From public.Covid_deaths dea
Join public.Covid_vax vax
    On dea.location = vax.location and dea.date = vax.date
where dea.continent is not null
view postgresql sql
1 Answers

You are doing two things:

  1. you create a table percent_pop_vax and fill it with the result of a query

  2. you create a view percent_pop_vax defined by the same query

That cannot work, because tables and views occupy the same namespace, so there cannot be a table and a view with the same name (in the same schema).

In my opinion, you should simply omit step 1 and create only the view. A view is just an SQL query with a name, and whenever you use the view, the query is executed to calculate the data on the fly. The table is quite superfluous.

If you want a view that actually stores the data like a table, you need a materialized view.

user_64643080
Related