We shall reproduce a serialization failure and see SERIALIZABLE isolation level enforces data integrity. Prerequisites We shall need the following test data: CREATE TABLE Carpools(  Car_Name VARCHAR NOT NULL,  Passenger VARCHAR NOT NULL);INSERT INTO Carpools(car_name, passenger)VALUES('Carol''s car', 'Jim'),('Carol''s car', ...

In this post we shall run some examples under REPEATABLE READ, and see how they behave differently. Setting up test data On SQL Server, run the following:CREATE TABLE Tickets(   ID INT NOT NULL,   Problem VARCHAR(100) NOT NULL,   SpaceFiller CHAR(200) NOT NULL);INSERT INTO Tickets(   ID ,   ...

As we have just discussed, READ COMMITTED isolation level behaves very much like Sql Server's READ_COMMITTED_SNAPSHOT. As such, we need to be very careful with data integrity - lots of code that just works on Sql Server under its default isolation level, READ COMMITTED, does not work on PostgreSql under its default isolation level, which is also ...

Because of multi-valued concurrency control aka MVCC, there are many differences in queries' behavior with different isolation levels. Before running examples, let us set up test data. Setting up test data The following script sets up the data we shall be playing with: DROP TABLE test;CREATE TABLE test(ID INT NOT ...

PostgreSql has simple and consistent error handling, which can be roughly explained in Sql Server terms as follows: XACT_ABORT is Always ON. In other words, error handling in PostgreSql has substantially less features, but it does have all the features which we actually use in the project being migrated. Simplicity of error handling in ...

UPDATE...FROM command on PostgreSql may raise no errors, but produce completely different results. Later I shall provide a repro, but first let us briefly refresh how UPDATE...FROM works on Sql Server. UPDATE...FROM on SQL Server ignores ambiguity We shall need the following test data: CREATE TABLE #Problems(ProblemID INT NOT ...

All SQL Server queries using TOP and/or APPLY need to be changed - PostgreSql uses completely different syntax. Replacing TOP with LIMIT The following script shows how to do that: CREATE TEMP TABLE Runs(State_Code VARCHAR,Run_Date DATE,Distance FLOAT,Description VARCHAR);INSERT ...

Constraints in PostgreSql are implemented somewhat differently. To use them efficiently, there are quite a few details we need to be aware of. NULLs and uniqueness In PostgreSql, unique constraints allow multiple NULLs. This behavior is ANSI standard. SQL Server's implementation of unique constraints is not ANSI standard.  The following ...

PostgreSql 9.3 allows us to declare parameter types to match column types, aka Copying Types. Also it allows us to omit the length of VARCHAR fields, without any performance penalty. These two features make PostgreSql a great back end for agile development, because they make PL/PgSql more resilient to changes. Both features are not in SQL Server ...

PostgreSql features multi-version concurrency control aka MVCC. To implement MVCC, old versions of rows are stored right in the same table, and this is very different from what SQL Server does, and it leads to some very interesting consequences. Let us play with this thing a little bit, but first we need to set up some test data. Setting ...