Postgresql exception-catching rocks!

Gotta love Postgres. It always yield back the control to you when an exception occur.

Given this:

?

1 2 3 4 5

create table z ( i int not null primary key, zzz int not null );

Try both(one at a time) alter table z drop column aaa; and alter table z add column zzz int;, your code can detect the DDL exceptions

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

do $$     begin       -- alter table z drop column aaa;     alter table z add column zzz int;     exception when others then       raise notice 'The transaction is in an uncommittable state. '                      'Transaction was rolled back';       raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE; end;     $$ language 'plpgsql';

Here are the errors, both kind of errors are catchable:

Contrast that with Sql Server, try both(one at a time) alter table z drop column aaa; and
alter table z add zzz int;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

begin try       begin transaction       -- alter table z drop column aaa;     alter table z add zzz int;       commit tran;   end try begin catch       print 'hello';     SELECT         ERROR_NUMBER() as ErrorNumber,         ERROR_MESSAGE() as ErrorMessage;       IF (XACT_STATE()) = -1     BEGIN         PRINT             N'The transaction is in an uncommittable state. ' +             'Rolling back transaction.'         ROLLBACK TRANSACTION;     END;   end catch   print 'reached';

Here are the errors for SQL Server:

Catchable error:


Uncatchable error:

Sql Server won't let you catch the error on alter table z add column zzz int;