CODEWITHSUNDEEP
sqlpostgresql
Elazar Leibovich
Elazar Leibovich

Reputation: 33623

Reverse SQL query schema modification

I'm given an SQL query that change the database schema in a certain way. How can I produce an SQL query that will reverse the schema change of the given query, so that executing the original query and the reverse query would have no effect on the schema.

For example, given

ALTER TABLE t ADD COLUMN a INTEGER

I'll produce

ALTER TABLE t DROP COLUMN a

PS, I understand that I might lose some data, for instance if I drop and create a column the data there would be lost. I can withstand this.

PS2, I assume that I can look into the current schema, to find out what dropped column/tables were.

PS3, generic answer is nice, but DB specific answers are OK as well.

Upvotes: 2

Views: 838

Answers (3)

Elazar Leibovich
Elazar Leibovich

Reputation: 33623

My best bet so far seems, applying the change, and runnign apgdiff on both schemas.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

In the general case, you can't (on PostgreSQL at least) unless you execute a CREATE TABLE statement. For example, let's say you start with this table.

CREATE TABLE wibble (
  a INTEGER,
  b INTEGER,
  c VARCHAR(35),
  d DATE
);

The schema will be recorded like this.

CREATE TABLE wibble
(
  a integer,
  b integer,
  c character varying(35),
  d date
)

Then you drop column "a", and add column "a".

ALTER TABLE wibble DROP COLUMN a;
ALTER TABLE wibble ADD COLUMN a INTEGER;

Now the schema will look like this.

CREATE TABLE wibble
(
  b integer,
  c character varying(35),
  d date,
  a integer
)

Column "a" was the first column; now it's the last. That doesn't matter a bit within relational theory or within SQL, because the order of columns doesn't matter. It matters a lot to a version control system, though.

Dropping a column might also drop constraints, triggers, and indexes.

PostgreSQL will also rewrite your SQL DDL statements into a canonical form. For example, you might write something like this.

CREATE TABLE wibble (
    a INTEGER PRIMARY KEY,
...

But PostgreSQL will store something like this instead.

CREATE TABLE wibble
(
  a integer NOT NULL,
  . . .
  CONSTRAINT wibble_pkey PRIMARY KEY (a)
)

Upvotes: 2

Peter Eisentraut
Peter Eisentraut

Reputation: 36749

There is no ready-made, built-in solution for this. You will have to write some code, possibly a lot.

There are a number of "schema diff" type tools out there. You could possibly put one of those into use. For example, create your original schema, make a copy, run the schema-altering command on the copy, then run a reverse schema diff, which should give you the command that undoes the first command. Depending on your exact requirements, you could script this to some degree. But it might be messy.

Upvotes: 0

Related Questions