SQL error

Fix “Is of Type View, Trying to Drop Type Table” in SQL

DROP TABLE will not remove a view, and IF EXISTS does not rescue it. Drop the type you made.

The symptom

DROP TABLE customer_revenue fails, and the message tells you why: the name exists, but it belongs to a view. Every DROP statement names a kind of object, and the kind has to match.

DuckDB says Catalog Error: Existing object customer_revenue is of type View, trying to drop type Table. The view is untouched. Nothing was half-dropped, which is worth knowing before you go looking for damage.

IF EXISTS does not help

This is the part that costs people time. DROP TABLE IF EXISTS guards against the object being absent, and that is all it guards against. A name held by the wrong kind of object still raises the same error, because the object does exist.

The guard covers absence, not type

create or replace view customer_revenue as
select customer_id
from customers;

drop table if exists customer_revenue;

In a setup script that matters more than it looks. The failing statement stops the script, so every statement after it never runs - and a teardown block full of IF EXISTS lines reads as though it cannot fail.

Drop the type you created

One statement per kind of object. The name gives you no clue about which one you need, so when a script is not sure, ask the catalog rather than guessing.

Object	Statement
Table	`DROP TABLE name`
View	`DROP VIEW name`
Schema	`DROP SCHEMA name`
Sequence	`DROP SEQUENCE name`
Macro or function	`DROP MACRO name`
Type	`DROP TYPE name`

Ask what the name actually is

create or replace view customer_revenue as
select customer_id
from customers;

select table_name,
       table_type
from information_schema.tables
where table_name = 'customer_revenue';

table_type comes back as VIEW or BASE TABLE, which is the answer you needed. DuckDB also has duckdb_views() andduckdb_tables() if you would rather query one list than filter a combined one.

Then drop it as a view

drop view if exists customer_revenue;

select count(*) as views_left
from duckdb_views()
where view_name = 'customer_revenue';

How the name got reused

Usually a report started as a view and was later promoted to a stored snapshot, or the other way around, and the teardown line was never updated. The fix that holds is to make the script own both possibilities, so it does not depend on remembering which one last ran.

Clear the name whichever it is

drop view if exists customer_revenue;
drop table if exists customer_revenue;

create table customer_revenue as
select customer_id,
       count(*) as orders
from orders
group by customer_id;

select count(*) as rows_now
from customer_revenue;

Order matters: with the view dropped first, the second line finds nothing and its guard does its job. Reversed, the DROP TABLE would hit the view and fail.

CASCADE does not rescue this either

CASCADE is about dependent objects, not about type mismatches, and in DuckDB it does less than you might expect even for that: DROP TABLE ... CASCADE does not drop views built on the table. The views survive and break at query time instead. Seethe DROP statement reference for what each form does.

How other engines word it

Engine	Message
DuckDB	`Catalog Error: Existing object customer_revenue is of type View, trying to drop type Table`
PostgreSQL	`ERROR: "customer_revenue" is not a table HINT: Use DROP VIEW to remove a view.`
MySQL	`Unknown table 'shop.customer_revenue'`
SQL Server	`Cannot drop the table 'customer_revenue', because it does not exist or you do not have permission.`
SQLite	`use DROP VIEW to delete view customer_revenue`