Write a Rerunnable Script

A setup script that only works on an empty database is a script you will be afraid to run. An idempotent script is one you can run again without thinking: the second run leaves exactly the state the first one did, and does not stop halfway with "object already exists".

SQL gives you three guards for this, and they are not 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"

This is the guard you want almost everywhere. It redefines the object to whatever the script says, whether or not it already existed, so the script text is the single source of truth for the definition. Views, tables, sequences, and macros all take it.

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 does not 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, becauseCREATE 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 does not survive the object being the wrong type: if a view of that name exists, DuckDB still raisesExisting 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

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 theDROP statement reference and theSQL schemas reference.