Write a Rerunnable Script

A setup script that only works on an empty database is difficult to operate safely. An idempotent script is designed so that, given the same source inputs, rerunning it converges on the same intended state instead of duplicating work or stopping on "object already exists". Rerunnable doesn't mean risk-free: replacement statements can still overwrite data or definitions.

SQL gives you three guards for this, and they aren't interchangeable. Picking the wrong one is how a script comes to look like it applied a change that it quietly skipped.

CREATE OR REPLACE means "make it match this"

Use this form when replacing the existing object is actually the operation you intend. It redefines the object to match the script whether or not it already existed. In DuckDB, views, tables, sequences, and macros support OR REPLACE, but the consequences differ: replacing a table overwrites the existing table, and replacing an object with dependencies may be blocked or have wider effects.

create or replace view active_customers as
select customer_id,
       first_name || ' ' || last_name as customer_name
from customers
where is_active

IF NOT EXISTS means "leave whatever is there"

This one doesn't fail on a second run either, but for a different reason: if the object exists, the statement does nothing at all and reports no error. The state column added below never appears, and nothing tells you so.

create view if not exists active_customers as
select customer_id,
       first_name || ' ' || last_name as customer_name,
       state
from customers
where is_active

Use it for the containers rather than the contents. A schema is the clear case: it holds no definition of its own, so leaving the existing one alone is exactly right. In DuckDB it is also the only workable choice, because CREATE OR REPLACE SCHEMA refuses to run once the schema holds anything: Cannot drop entry "reporting" because there are entries that depend on it.

DROP IF EXISTS only guards against absence

A cleanup line like drop table if exists active_customers survives the object not being there. It doesn't survive the object being the wrong type: if a view of that name exists, DuckDB still raises Existing object active_customers is of type View, trying to drop type Table, and every statement after it in the script is skipped. Drop the type you actually created.

The shape of a rerunnable script

A useful setup pattern is containers first with IF NOT EXISTS, then the objects with OR REPLACE, then a verification query so the script finishes by showing you what it built rather than leaving you to guess.

-- 1. containers, guarded by IF NOT EXISTS
create schema if not exists reporting;

-- 2. objects, redefined every run
create or replace view reporting.active_customers as
select customer_id,
       state
from customers
where is_active;

-- 3. verification, so the script ends by proving it worked
select count(*) as active_customers
from reporting.active_customers

Run it again. Same seven rows, no errors, nothing duplicated, which is the whole point. For the object-by-object detail, see the DROP statement reference and the SQL schemas reference.