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

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, so there is no damage to go looking for.

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.

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. In SQLShed and clients that stop a batch on the first error, statements after the failure do not run, even though a teardown block full of IF EXISTS lines may read 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.

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() and duckdb_tables() if you would rather query one list than filter a combined one.

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.

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.

How other engines word it

Related errors

• Fix “Referenced Column Not Found” in SQL

  No table in the FROM clause has a column by that name. Usually a typo or a table you did not join.

• Fix “Cannot Insert Into a Generated Column” in SQL

  A generated column computes itself. Leave it out of the column list entirely.

• Fix “Syntax Error at or Near” in SQL

  The parser stopped at the first word it could not use. The quoted token is where it gave up, not always where the mistake is.

Learn more

• SQL error messages explained
• Reference · DROP statements
• Reference · SQL views
• Lesson 18.5 · Write a rerunnable script