Postgresql exception-catching rocks!

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

Given this:

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

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;

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;