postgreSQL创建一个带有临时表的视图

Question

我需要一些帮助从临时表创建视图。我是相当新的SQL，所以我不理解的错误信息，正在发送给我，当我试图创建一个视图。

错误信息:

SQL Error [42P07]: Error: relation "percent_pop_vax"已经存在。

我还没有从临时表创建视图，所以我不知道为什么会发生这种情况。我已经使用了CTE方法，这似乎是有效的。请帮助我了解如何使用临时表的视图。谢谢你！

-- 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

Answer 1

你在做两件事:

1. 创建一个表percent_pop_vax并填充查询的结果

2. 你创建一个视图percent_pop_vax由相同的查询定义

这是不行的，因为表和视图占用相同的命名空间，所以不可能有一个表和一个视图具有相同的名称(在同一个模式中)

在我看来，您应该省略步骤1，只创建视图。视图只是一个带有名称的SQL查询，无论何时使用视图，都会执行查询以动态计算数据。这张桌子太多余了。

如果你想要一个像表一样实际存储数据的视图，你需要一个物化视图。