I am facing scenario, I have executed a changeset to create table and now did a change on create table. I want my changeset to drop table if exists and re create it. Is this possible with XML format in liquibase.
create table1 as select col1,col2,col3 from table2 where id in (1,2,3,4,5);
Hi! Is there a modern equivalent of this please? Iām guessing this old answer from 2013 was using raw SQL, itās hard to tell as the forum seems to have swallowed the XML markup.
Iām quite new to the runOnChange attribute of changeSet, and the replaceIfExists attribute on createProcedure, so I may veer off track here
Iāve got a changeset XML file that wants to create a table, create a stored procedure that will populate that table, then run the stored procedure. Unfortunately the createTable step fails the second time around because the table already exists.
I can mark the changeset as runOnChange="true", and the createProcedure as replaceIfExists="true", but I cannot use replaceIfExists with createTable. I also donāt see a way to use dropTable only if the table already exists. It seems these features ought to be on the core createTable command, similar to SQLās CREATE OR REPLACE TABLE foo....
Am I missing something obvious? Iāve Googled around but there doesnāt seem to be a good answer here. I imagine the best solution may be to split the changeset into two, one which creates the table, and one which runs the createProcedure replaceIfExists command, but if the table definition needs to change too then the procedure would fail when it is re-run.
Thank you very much for the detailed response, I really appreciate your
time.
Ah yes, splitting one XML changeLog file into multiple changeSets has
always seemed weird to me, because Iām logically rolling out āoneā
feature to our site, I add the single XML file to the changelog.xml file
and issue a single āapplyā command to run all the changesets that are
outstanding, then if I want to rollback Iād like to logically rollback
just āoneā feature. If I split a single XML file into three changeSets
as you (probably rightly) suggest, then if I need to rollback the entire
feature I need to dig into it and be damn careful how many changesets I
give to the rollback command, or I might rollback a changeSet from a
dusty old feature I forgot about.
I suppose this is par for the course, and of course Iām just one
developer using one of the many different use cases for Liquidbase.
From what you say, there is no āCREATE OR REPLACEā operation for tables
(Iām on MariaDB by the way) so Iāll stick with a single DDL changeSet
with a create and rollback-drop workflow, as you suggest.