CODEWITHSUNDEEP
postgresql
Callum Rogers
Callum Rogers

Reputation: 15829

How to rename a postgres table only if the target table name does not exist

I want to rename a table as part of a migration, but only if the table has not been renamed already. I'm currently running:

ALTER TABLE IF EXISTS old_name RENAME TO new_name

however, if new_name already exists it will still run. Is there a way to run this statement if and only if new_name does not exist?

Upvotes: 2

Views: 3786

Answers (1)

Matthias
Matthias

Reputation: 178

You could always write an anonymous code block (or also a user defined function/procedure) similar to:

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'z') THEN  
        EXECUTE 'ALTER TABLE a RENAME TO z';
    END IF;
END$$;

As far as I know you cannot specify it with the ALTER TABLE command directly.

Upvotes: 9

Related Questions